[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 380
  • Last Modified:

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?
0
Ricky66
Asked:
Ricky66
  • 2
  • 2
  • 2
  • +5
5 Solutions
 
yawkey13Commented:
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
 
käµfm³d 👽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
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
Anthony PerkinsCommented:
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 TomoskyTechnology ConsultantCommented:
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
 
deightonCommented:
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
 
agusacilCommented:
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 ShahCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
  • 2
  • +5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now