Solved

Slow Running simple SQL Script...

Posted on 2014-01-19
9
205 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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.
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.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

840 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