Solved

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

Posted on 2014-12-21
6
115 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 49

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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

729 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