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...

Recommended Answers

All 3 Replies

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?

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...

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.