SQL Query taking forever

Question

Hi,

I have this webapplication tool which queries over data and shows it in a grid. Now a lot of people use it so it has to be quite performant.

The thing is, I needed to add a couple of extra fields through joins and now it takes forever for the query to run.

If I in sql server run the following query:

select top 100 *
from bam_Prestatie_AllInstances p
join bam_Zending_AllRelationships r on p.ActivityID = r.ReferenceData
join bam_Zending_AllInstances z on r.ActivityID = z.ActivityID
where p.PrestatieZendingOntvangen >= '2010-01-26' and p.PrestatieZendingOntvangen < '2010-01-27'

This takes about 35-55seconds, which is waaay too long. Because this is only a small one.

If I remove one of the two date checks it only takes 1second. If I remove the two joins it also takes only 1 second.

When I use a queryplan on this I can see that 100% of the time is spend on the indexing of the PrestatieZendingOntvangen field. If I set this field to be indexed, nothing changes.

Anybody have an idea what to do?

Because my clients are starting to complain about time-outs etc.

Thanks

Answer 1

Try between?

where p.PrestatieZendingOntvangen 
   between '2010-01-26 00:00:00' and '2010-01-27 23:00:00'

Answer 2

1. Specify the fields you want to retrieve, rather than *
2. Specify either Inner Join or Outer Join

Answer 3

Have you placed an indexes on the date fields in your Where clause.

If not, I would create a INDEX on that fields to see if it makes any differences to yourv time.

Of course, Indexes will take up more disk space so you will have to consider the impact of that extra index.

EDIT:

The others have also made good points about specifing what columns you require in the Select instead of * (wildcard), and placing more indexes on foreign keys etc.

Answer 4

Besides the obvious question of an index on the bam_Prestatie_AllInstances.PrestatieZendingOntvangen column, also check if you have indices for the foreign key columns:

• p.ActivityID (table: bam_Prestatie_AllInstances)
• r.ReferenceData (table: bam_Zending_AllRelationships)
• r.ActivityID (table: bam_Zending_AllRelationships)
• z.ActivityID (table: bam_Zending_AllInstance)

Indexing the foreign key fields can help speed up JOINs on those fields quite a bit!

Also, as has been mentioned already: try to limit your fields being selected by specifying a specific list of fields - rather than using SELECT * - especially if you join several tables, just the sheer number of columns you select (multiplied by the number of rows you select) can cause massive data transfer - and if you don't need all those columns, that's just wasted bandwidth!

Answer 5

Someone from DB background can clear my doubt on this.

I think, you should specify date in the style in which the DB will be able to understand it.
for e.g. Assuming, the date is stored in mm/dd/yyyy style inside the table & your query tries to put in a different style of date for comparison (yyyy-mm-dd), the performance will go down.

Am I being too naive, when I assume this?

Answer 6

How many columns does bam_Prestatie_AllInstances and the other tables have? It looks like you are oulling all columns and that can definitely be a performance issue.

Have you tried to select specific columns from specific tables such as:

select top 100 p.column1, p.column2, p.column3

Instead of querying all columns as you are currently doing:

select top 100 *