Solved

Slow Running simple SQL Script...

Posted on 2014-01-19
9
204 Views
Last Modified: 2014-02-08
I have a simple SQL Script that is running against EMPTY tables that are NOT INDEXED. Again no data is in these tables are present. The query takes about 18 seconds to process and return BLANK results. We need it to be processed in 2-3 seconds.

QUESTION: Does the fact that the 4 tables involved are not INDEXED have any bearing on how quickly the query is returning blank results....?

Thanks
0
Comment
Question by:MIKE
9 Comments
 
LVL 17

Accepted Solution

by:
MIKE earned 0 total points
ID: 39792386
Update,... it was just explained to me that the "tables" involved are actually,... VIEWS... so any additional comments are appreciated, but I'm going to start looking at each view to see if any issues.
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 167 total points
ID: 39792388
If there is no data in the tables then it should have no bearing if things are indexed or not.
0
 
LVL 17

Author Comment

by:MIKE
ID: 39792413
When I query certain columns, say "SELECT T1.Col1, T2.Col2, T3.Col3" from the various VIEWS ..the query processes in 18 seconds.

When I do "SELECT *" on these same VIEWS,..it processes in 2 seconds...

WHY?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 84 total points
ID: 39792477
first... do update your statistics

Run the below stored procedure

sp_updateStats

Open in new window


then execute the query again... this happens when the table used to contain a lot of data earlier and now it was truncated and the statics are stale.
0
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 167 total points
ID: 39792595
Views - do they have any WHERE clause (or other filter) in them?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 83 total points
ID: 39792686
So the Views do not return any data, what about the underlying tables how many rows do they contain?
0
 
LVL 11

Assisted Solution

by:Kusala Wijayasena
Kusala Wijayasena earned 83 total points
ID: 39793544
Could you please paste you SQL Script here ?
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 83 total points
ID: 39798431
You can have a very complex view that calls different functions but returns nothing because of the filtering done by the join, where or having-clause. (as others indicate before)

Look at the execution plan of your query that will give you an indication  where that time is going.
Maybe the indexes on the underlying tables are not good in combination with the filtering/joins and you need to dig trough the whole table.

Need for updated statistics and index maintenance are also a possibility but only after you have seen where your time is lost.  Always see first, change 1 thing, look again, .... if not you can change  10 things and don't know what did do the trick.
0
 
LVL 17

Author Closing Comment

by:MIKE
ID: 39843894
good
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

823 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