Solved

Slow Running simple SQL Script...

Posted on 2014-01-19
9
208 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
[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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

707 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