Help with Unions (can i do this differently?)

[Glowworm27]

Hello all,

I have a proc that will return exceptions from a HUGE table of data. The exceptions are a set of rules the program checks before data can be sent to our processing company.

For instance we check for records where dates are wrong, amounts are incorrect, to big, or a negative amount, and many, many more. We actuall check for about 20-30 differnt things.

I created a query that would return the record ID, and text that described which rule was violated, and then use a UNION statement to return the next rule, and so on and so forth.

Well as our data has built up this query is taking longer and longer and now it is timing out very frequently!

Is there some way I can get a recordset of all these exceptions into one recordset? and without timing out????

Thanks
G

George Oakes
CEO & President
COPS Software, Inc.

http://www.cops2005.com

Programmer & Developer
.Net, WSS 3.0, SQL DBA
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net

 

[gmmastros]

Try UNION ALL instead of UNION.

When you use union, it checks to see if the data is distinct (which is an expensive/slow operation). UNION ALL will allow duplicates (and therefore doesn't check for distinct). Since you are using an ID, but the description are different, all your data will be unique anyway.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom