SQL Server Performance

Question

I have tables in which 35000 to 40000 records are available. Inspite using ajax the performance of the website is very low. Can any body please suggest some ideas or tips for the problem. Thanks in advance.

Answer 1

Add indexes to the columns involved in your queries, but honestly without some more information, like the structure of your table and the indexes it contains and the queries you want to speed up, this is impossible to answer.

Are you sure that it is the database queries that are the problem and not some other factor involved in the Ajax or some other aspect of your website code?

I would run the queries in the database alone to see if that really is the problem first, before I spent a lot of time optimizing something that might not even be the problem.

You might also want to consider profiling your code to see where the bottle neck really is.

There are many questions which outline options for profilers in various languages .Net, Php, Java, C++, Erlang. You get the idea...

You can also use the Microsoft Sql Server Profiler to profile the database and get some insight into where the time is being spent, if indeed the queries are slow.

Answer 2

Since those are tiny, tiny tables, it is likely that either you have written very bad queries or something else is the problem. Even with table scans (assuming no indexing), queries on a table that small shouldn't be all that slow.

Sam Holder is right, you need to profile first to find the real bottleneck before anyone can help you beyond a guess. However if you don't have indexes on your FK fields, you should. If you haven't properly defined PKs and FKs, then you should do that as well.