Can this run faster? - 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)

Can this run faster?

Author

Topic

jhidey
Starting Member

2 Posts

Posted - 2002-08-27 : 15:03:59

Hello everyone,

I have the sql statement that I came up with to give me a small report but it seems kinda slow to me. The table layout is like this.

tblColorVersions has two support tables (tblsColorColorantSystem & tblsFormulaStatus) I need the sum or each colorant/status combination. Even if there are no records in the part table (tblColorVersions) I will need a return showing a count of 0 for that particular colorant/status combination.

Any help would be greatly appreciated.

Now here is my SQL

SELECT
CS.intColorColorantSystemId AS [SystemId],
CS.strColorColorantSystem AS [System],
S.intFormulaStatusId AS [StatusId],
S.strFormulaStatus AS [Status],
COUNT(S.intFormulaStatusId) AS [SystemCount]
FROM
tblColorVersions V
CROSS JOIN (tblsColorColorantSystem CS CROSS JOIN tblsFormulaStatus S)
WHERE
CS.intColorColorantSystemId=V.intColorColorantSystemId AND
S.intFormulaStatusId=V.intFormulaStatusId
GROUP BY ALL
CS.intColorColorantSystemId,
CS.strColorColorantSystem,
S.intFormulaStatusId,
S.strFormulaStatus
ORDER BY
strColorColorantSystem,
Status

Thanks ahead of time everyone.

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-08-27 : 21:32:11

How slow is kinda slow? How many records are we talking about? - 100, 1000, 1000000000?

Do you indexes applied to the join fields?

I get a little lost in all the cross joins - can you pre-populate a table with all the combinations and just to the aggregate calculations?

jhidey
Starting Member

2 Posts

Posted - 2002-08-27 : 22:09:48

Well, slow is in the ballpark of 10-20 seconds. There are roughly 1/2 million rows in tblColorVersions and the two other tables each have less than 10 records each.

In tblColorVersions I do not presently have the two foreign keys indexed. Hope this helps.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-08-28 : 11:51:08

Considering that you are, in effect, generating a 50,000,000 record table on the fly (500,000 * 10 * 10) and then doing GROUPing, I'm impressed that it's taking ONLY 20 seconds to do.

But if you want to speed it up, testing different indexing scenarios is certainly a good place to start. And so is checking the Execution Plan that QA generates.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources