SQL Query slowing down

[jshanoo]

Hi,
I have the following query , it is consuming more than 6 mts to retieve the result, i want to make it faster retrieval.
Query is fetching results from views , which in turn consists fetches only distint records.
Due to the non-unique feature of the data, i cannot relate the table with the respective fields.

I used sql profiler and used index tuning wizard to speed up the process.


Can any one suggest the best laternative way for the same

Best Regards
John Philip

'----------------------------------------------------
Select A.name,b.contract,b.class,b.iro,b.date,b.time,
b.prefix,b.caller,b.phone,b.email,b.company,b.areaname
from vwuniqueclass A,vwFeedback B where A.code=b.class
and A.name >= 'CAR-MARUTI BALENO'
and A.name <= 'COMPUTER-UNIQUE'
and (B.date between '03/03/2004' and '06/03/2004') order by b.date,b.time
'----------------------------------------------------

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[vongrunt]

This query is too simple... post source code for views.

Btw. how many records have vwuniqueclass and vwFeedback? And do you have index on vwFeedback.date?

 

[jshanoo]

HI,
the data size i s huge, somwwhere around 5-6 lacs of records in both tables
'-------------------------
vwfeedback
SELECT DISTINCT *
FROM dbo.feedback
vwuniqueclass
SELECT code, MAX(name) AS name
FROM dbo.class
GROUP BY code

Regards
John

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[vongrunt]

Filter conditions (name, date) are relatively narrow... and query lasts 6 minutes. It is very likely that views materialize completely before WHERE clause. How long it takes this query to execute (equivalent without views)? :

Select A.name,b.contract,b.class,b.iro,b.date,b.time,
b.prefix,b.caller,b.phone,b.email,b.company,b.areaname
from 
(	select distinct * from dbo.feedback where name between 'CAR-MARUTI BALENO' and 'COMPUTER-UNIQUE' ) a
inner join 
(  select code, max(name) as name from dbo.class where date between '03/03/2004' and '06/03/2004' group by code ) b
on A.code=B.class
order by B.date, B.time

Btw. looks like feedback/class are denormalized mess. Is DISTINCT * over dbo.feedback really necessary? Max(name) from dbo.class?

If physical model cannot be changed, try using indexed views ( vwuniqueclass.name and vwFeedback.date ).

 

[jshanoo]

Hi,
Distinct and max are needed to avoid duplicancy.
The database is not at all normalized.

I dont know much about indexex views can u please help me in that.
mean time ia m working on the query sent by you.
Regards
John philip

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[jshanoo]

Hi,
I have trie dur query its going more than 10-11 minutes

regards
John Philip

*** Even the Best, did the Bad and Made the Best ***

John Philip

 

[vongrunt]

Try to get rid of DISTINCT *... it often causes table scan no matter what indexes you have. And if any (set of) attributes has assigned primary key or UNIQUE index, it is pointless too.

If table vwFeedback is very long and/or has wide records, consider table partitioning (horizontal or vertical).

IMO I'd not recommend any hi-tech fix (indexed views included) because they don't solve core problem (duplicate data).