Solved

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

Posted on 2014-12-21
6
109 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

11 Experts available now in Live!

Get 1:1 Help Now