Solved

How to reduce the runtime of a SQL Server SQL statement using SQL Server 2008?

Posted on 2014-12-21
6
114 Views
Last Modified: 2014-12-22
I am writing an Access application that uses SQL Server as the back end database.

I run the following query which takes over 1 minute and 30 seconds to execute.

Any suggestions to speed it up?

dbo.tblFlINT has a data structure as follows with 18,000 records:

[Account Number]  nvarchar(10)
[Address 1]               nvarchar(35)
...
[MarginInd]              nvarchar(1)

dbo.tblIntlAccountFallExcl has a data structure with just 1 field as follows with only about 20 records:

Account                    nvarchar(10)


SELECT Distinct [Account Number] FROM dbo.tblFlINT WHERE [Account Number] NOT IN
(SELECT Account FROM dbo.tblIntlAccountFallExcl) ORDER BY 1
0
Comment
Question by:zimmer9
  • 3
  • 2
6 Comments
 
LVL 17

Expert Comment

by:Chris Millard
ID: 40512173
If you run the query in SQL itself, how long does it take for the result to come back?
0
 

Author Comment

by:zimmer9
ID: 40512179
When I run it from SQL Server Managment Studio it takes over 90 seconds.
0
 

Author Comment

by:zimmer9
ID: 40512181
select * from dbo.tblFlINT                        takes 15 seconds from SQL Server Managment Studio
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 17

Expert Comment

by:Chris Millard
ID: 40512185
And are you running the Access app on the same machine as the SQL DB? If not, can you try and see if the results are the same?
0
 
LVL 17

Expert Comment

by:Chris Millard
ID: 40512189
Ignore my last question - I missed your second to last comment
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40512544
What indexes exist on these tables? If indexing does not cover your query fixing that will probably make the biggest difference.
DISTINCT is the enemy of performance do you really need it?
IN, or NOT IN, can also be slow. Try NOT EXISTS instead of NOT IN
SELECT
      [Account Number]
FROM dbo.tblFlINT AS FlINT
WHERE NOT EXISTS (
      SELECT NULL
      FROM dbo.tblIntlAccountFallExcl as EXCL
      where FlINT.[Account Number] = EXCL.Account
      )
ORDER BY
      [Account Number]
;

Open in new window

0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Compare a column in results by values left of decimal 2 22
SQL Pivot table 2 45
Union & Crosstab qrys 101! 6 58
UPDATE JOIN multiple tables 5 23
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question