Solved

Slow Running simple SQL Script...

Posted on 2014-01-19
9
206 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

713 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