Member Avatar for OnlyTruth

Hi,

I am having a table with 16 lakhs records in it. Each record contains approximately 50 columns. I am using MSSQL Database. Basically the table contains details of the user's registration details. I have a application built in VB.net which will be selecting a random user_id and then searches for the record which contains that particular id and retrieves the details . The user_id column in my table is set as primary key. My Query is as given below:

SELECT * FROM user_Table WHERE user_id=MyNumber

The value for MyNumber is passed from the application. The problem is that the application takes much time to execute this query.(almost 15 secs for per 1000 users) which is very slow. I need to increase the speed by atleast 50-70%. Currently I am using ADODB recordset for this query. I have also tried using datasets but no improvement in the speed. Is there any way to optimize the SQL Server's performance or do I need to change my query. Please Help...

  • 3 Contributors
  • 3 Replies
  • 241 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by campkev

Dani AI

Generated

has a big table (about 1.6M rows, ~50 columns) and is doing many single-row lookups from a VB.NET client using an ADODB recordset; and already steered the thread toward indexing and testing in Query Analyzer. The fastest way to narrow the cause is to determine whether the delay is on the server or the client.

  • Server-side checks (run these on the server with the same parameter value): enable statistics and check the execution plan to confirm an index SEEK (not a scan), and note logical reads and CPU/time with SET STATISTICS IO ON and SET STATISTICS TIME ON. If the plan is doing a SEEK, the server work for a single PK lookup should be tiny; large logical reads or a scan mean index/statistics problems. Rebuild or reorganize heavily fragmented indexes and update statistics if they are stale.

  • Client-side and data-model fixes: ADODB recordsets can incur extra overhead and metadata round-trips. For .NET, using SqlClient with a forward-only, read-only reader is leaner and avoids extra fetching. Also avoid pulling all 50 columns if you only need a few—create a narrow covering index or include frequently read columns so the engine doesn’t need a lookup. If wide TEXT/BLOB columns exist, vertically partition them into a separate table so the hot path reads a much smaller row.

  • About partitioning: partitioned tables help maintenance, large-range scans and archiving, but will rarely speed up single-row PK lookups by themselves. Vertical partitioning or a covering index usually gives much bigger wins for point queries.

Quick checklist: verify server vs client timing, inspect execution plan, update stats/rebuild indexes, remove SELECT * (return only needed columns), move large columns out, and switch the client to SqlClient/SqlDataReader (CommandBehavior.SingleRow) or a properly prepared stored procedure.

Recommended Answers

All 3 Replies

Member Avatar for Letscode

Is userID declared as a primary key?(In order for it to have an index)
R U using somekind of a stored procedure to get the values?

Member Avatar for OnlyTruth

Ya, The Userid is declared as primary key. I m not using any stored procedure to get the values. I m using ADODB recordset for querying the table. I have tried using stored procedure but the speed was almost same n the other thing is that the dirty value in the procedure cache goes on increasing. Thats why i switched back to my recordset method. I wanted to know whether there is any method of logically partioning the table so that the query retrieves data quickly.
Thanks For Ur Reply buddy...

Member Avatar for campkev

how long does the query take to run if you run it from query analyzer?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.