Sql queries faster?

We are using Sql Server 2k and a VB 6 application.

I find some of the SQL queries take about a minute to retrieve a single record. But then if I try to retrieve the same record a second time it loads pretty quickly in 5 seconds.

What are some of the ways to optimize SQL performance? Would upgrading to newer SQL versions significantly improve performance?
Ricky66Asked:
Who is Participating?
 
käµfm³d 👽Connect With a Mentor Commented:
You need to create indexes on the table(s) being queried.
I think that should really say:  You need to create appropriate indexes on your tables. Indexes incur overhead, and too many indexes can actually cause slow down of your DB as well as not having them can. I'm sure yawkey13's intent revolved around this, but I just wanted to make sure it was stated that you have to be smart about how you create your indexes  = )

P.S.

As an FYI, the reason we create indexes on tables is because without them our queries will cause a "full table scan", and for a high-traffic site this will surely lead to customer flocking to a speedier competitor. Searching every row of the DB every time a query hits your DB is certain to cause performance degradation.
0
 
yawkey13Connect With a Mentor Commented:
You need to create indexes on the table(s) being queried.  If you supply one of the queries and the table definition, I can walk you through setting them up.
0
 
yawkey13Commented:
That is why I asked for this query and table structure.  My intent is to tell him what index(es) to create and why we are created them.
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Anthony PerkinsConnect With a Mentor Commented:
I find some of the SQL queries take about a minute to retrieve a single record. But then if I try to retrieve the same record a second time it loads pretty quickly in 5 seconds.
That is called caching.  SQL Server will keep the information in memory for a period of time after you query the information.  That is the reason it is so quick the second time.
0
 
Aaron TomoskySD-WAN SimplifiedCommented:
What's the size of the db and how much memory does the server have? Keeping the db in memory can sipped things up significantly if possible. If you can rdp into the server you can see if the query is CPU or io bound...
0
 
deightonprogCommented:
Indexes are the think to look at first here, I'd definitely go down that route before spending money - you might well be able to get 60 and 5 seconds down to zero seconds.
0
 
agusacilCommented:
Hi,

Agree with all experts above. It is most likely about missing index on that particular query.

For a complete check list to optimize your SQL Server application performance, see here
http://msdn.microsoft.com/en-us/library/ff647681.aspx
0
 
agusacilConnect With a Mentor Commented:
In addition, for your case upgrading to newer version of SQL Server will not improve performance if the necessary index for the query is not created.

New version of SQL Server always come with performance improvement over the older version, but it depends on whether or not the application make use of the new feature (e.g. small scale application may not get much improvement by upgrade). It is better to focus on the 'basic' things first before considering upgrade.
0
 
Nitin ShahConnect With a Mentor Commented:
Create Index for all Order by fields and check the difference.
0
 
Anthony PerkinsCommented:
I suspect the author no longer cares or has left the building...
0
 
Ricky66Author Commented:
Thanks all! The issue was with the Sql query.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.