Group By performance issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Group By performance issue

Author

Topic

maloy
Starting Member

19 Posts

Posted - 2006-07-11 : 20:42:09

I'm currently working on a product which uses the Informatica repository internally. One of the queries is of the form:
select distinct field1, field2, ... fieldn
from
(inline view view1),
(inline view view2),
(inline view view3)
where
(join conditions between inline views)

Out of these inline view view1 returns 57K rows, view2 returns 3K rows, view3 returns 2K rows and the entire query returns around 67K rows.
I have similar queries which have the same view1 but different view2 (returning 6 records) or view3 (returning 5 records). These queries finish in 2 or 3 seconds, but the 67K query takes around 19 mins.

I'm not sure how to tune the SQL for this and want some help from you guys.
I'm giving the structure of the inline view view1 here; please let me know if you need the entire query - I can provide that.

Inline view view1:
SELECT DISTINCT dep.mapping_id mapid, rel_widgetinst.widget_id relid,
dep.to_instance_id relinstid, dep.to_field_id relfldid,
dep.from_instance_id transforminstid,
dep.from_field_id transformfldid,
transform_widgetinst.widget_id transformid,
rel_widgetinst.instance_name relinstname,
transform_widgetinst.instance_name transforminstname,
rep_widget_field.expression AS mapexpr,
rep_widget_field.field_name AS field_name
FROM (SELECT MAX (version_number), mapping_id, from_instance_id,
to_instance_id, to_field_id, from_field_id
FROM opb_widget_dep
GROUP BY mapping_id,
from_instance_id,
to_instance_id,
to_field_id,
from_field_id) dep,
(SELECT MAX (version_number) transver, widget_id,
widget_type, instance_id, instance_name, mapping_id
FROM opb_widget_inst
WHERE widget_type NOT IN (1, 2, 11, 6)
GROUP BY widget_id,
widget_type,
instance_id,
instance_name,
mapping_id) transform_widgetinst,
(SELECT MAX (version_number) transver, widget_id,
widget_type, instance_id, instance_name, mapping_id
FROM opb_widget_inst
WHERE widget_type = 2
GROUP BY widget_id,
widget_type,
instance_id,
instance_name,
mapping_id) rel_widgetinst,
rep_widget_field
WHERE dep.mapping_id = rel_widgetinst.mapping_id
AND dep.mapping_id = transform_widgetinst.mapping_id
AND rel_widgetinst.mapping_id = transform_widgetinst.mapping_id
AND dep.from_instance_id = transform_widgetinst.instance_id
AND dep.to_instance_id = rel_widgetinst.instance_id
AND rep_widget_field.widget_id = transform_widgetinst.widget_id
AND rep_widget_field.field_id = dep.from_field_id

Here table opb_widget_dep has 230K records, and rep_widget_field which is actually a complex view has around 260K records.

TIA
Maloy

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-12 : 00:41:45

A couple of things you could try:
-In Query Analyser, have a look at the query Execution Plan as this might give you some pointers as to what's going wrong
-Run Profiler, capture the trace and feed it into the optimisation utility (can't remember its name off-hand)
-Create each one of your inline views into a proper view. SQL will then be able to optimise the view by preselecting the indexes it needs to use etc.

btw - you might want to convert your joins into the accepted INNER JOIN/ OUTER JOIN syntax. It will be easier to read.

HTH,

Tim

maloy
Starting Member

19 Posts

Posted - 2006-07-12 : 16:09:34

Hi Tim
Thanks for your reply.
I need to create indexes on the tables. I have only indexes on key fields right now.
Ctrl-L in QA shows that maximum cost is due to the hash join where the opb_widget_dep is joined with rep_widget_field.

Any other suggestions?

Thanks!
Maloy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources