Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Slow Running simple SQL Script...

Posted on 2014-01-19
9
Medium Priority
?
212 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 45

Assisted Solution

by:AndyAinscow
AndyAinscow earned 501 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 252 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 45

Assisted Solution

by:AndyAinscow
AndyAinscow earned 501 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 249 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 249 total points
ID: 39793544
Could you please paste you SQL Script here ?
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 249 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

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.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 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