Solved

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

Posted on 2014-12-21
6
111 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
Backup Your Microsoft Windows Server®

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

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

813 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now