Solved

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

Posted on 2014-12-21
6
110 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Complex SQL script 1 32
SQL Query resolving a string conversion issue 26 39
Managing Columnstore Indexes 2 19
SQL Pivot Rows To Columns 10 27
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

21 Experts available now in Live!

Get 1:1 Help Now