March 11, 2009 at 2:31 pm
Can this be rewritten somehow? I need to fine the ErrorCode on each column...and generated a report for review with the code similar to the below. The code is working, but with over 50+ millions records is taking a bit to return the result.
declare @tbl2 table(id int,col1 money ,col2 int,col3 varchar(20),col4 varchar(20),col5 varchar(20))
insert into @tbl2
values(1,10000,200,'yyyyyyy','yyyyy','yyyyyyyyyyyy')
insert into @tbl2
values(2,500 ,150,'yyy','yyyyyyy','yyyyy')
insert into @tbl2
values(3,600,60,'yyyyyyyyyyy','yyyyyyyyyy','yyyyyyyyyy')
select ID, ColumnWithError, CodeError from(
SELECT ID ,1 as ColumnWithError, 'NC' CodeError FROM @tbl2 WHERE ISNULL(col1,0)<1000
UNION ALL
SELECT ID,2 ,'NQ' FROM @tbl2 WHERE ISNULL(col2,0)<100
UNION ALL
SELECT ID,3 ,'NI' FROM @tbl2 WHERE len(ISNULL(col3,''))<5
UNION ALL
SELECT ID,4 ,'NY' FROM @tbl2 WHERE len(ISNULL(col4,''))<10
UNION ALL
SELECT ID,5 ,'NX' FROM @tbl2 WHERE len(ISNULL(col5,''))<10
)tbl1
order by ID
March 11, 2009 at 3:05 pm
First of all, replace ISNULL(colN,0)<NNN with (colN<NNN or colN IS NULL).
_____________
Code for TallyGenerator
March 11, 2009 at 3:14 pm
Are you in a position where you can either add columns to the table or create an indexed view on the table?
If you add some calculated columns and index them, then use those in your Where clause, you should get a MUCH faster query.
Here's a test I did on this concept:
set nocount on;
if object_id(N'tempdb..#T') is not null
drop table #T;
if object_id(N'tempdb..#T2') is not null
drop table #T2;
if object_id(N'tempdb..#T3') is not null
drop table #T3;
create table #T (
ID int identity primary key,
Col1 money,
Col2 int,
Col3 varchar(20),
Col4 varchar(20),
Col5 varchar(20));
insert into #T (Col1, Col2, Col3, Col4, Col5)
select
abs(checksum(newid()))%2000,
abs(checksum(newid()))%200,
replicate('y', abs(checksum(newid()))%20+1),
replicate('y', abs(checksum(newid()))%20+1),
replicate('y', abs(checksum(newid()))%20+1)
from dbo.Numbers; -- Currently 50,000 rows
set statistics time on;
select ID, ColumnWithError, CodeError
into #T2
from(
SELECT ID ,1 as ColumnWithError, 'NC' CodeError FROM #T WHERE ISNULL(col1,0)<1000
UNION ALL
SELECT ID,2 ,'NQ' FROM #T WHERE ISNULL(col2,0)<100
UNION ALL
SELECT ID,3 ,'NI' FROM #T WHERE len(ISNULL(col3,''))<5
UNION ALL
SELECT ID,4 ,'NY' FROM #T WHERE len(ISNULL(col4,''))<10
UNION ALL
SELECT ID,5 ,'NX' FROM #T WHERE len(ISNULL(col5,''))<10
)tbl1
order by ID;
set statistics time off;
alter table #T
add
Col1Val as isnull(Col1, 0) persisted,
Col2Val as isnull(Col2, 0) persisted,
Col3Len as len(isnull(Col3, '')) persisted,
Col4Len as len(isnull(Col4, '')) persisted,
Col5Len as len(isnull(Col5, '')) persisted
create index IDX_T_Val1 on #T (Col1Val);
create index IDX_T_Val2 on #T (Col2Val);
create index IDX_T_Len3 on #T (Col3Len);
create index IDX_T_Len4 on #T (Col4Len);
create index IDX_T_Len5 on #T (Col5Len);
set statistics time on;
SELECT ID ,1 as ColumnWithError, 'NC' CodeError
into #T3
FROM #T WHERE Col1Val<1000
UNION ALL
SELECT ID,2 ,'NQ' FROM #T WHERE Col2Val<100
UNION ALL
SELECT ID,3 ,'NI' FROM #T WHERE Col3Len<5
UNION ALL
SELECT ID,4 ,'NY' FROM #T WHERE Col4Len<10
UNION ALL
SELECT ID,5 ,'NX' FROM #T WHERE Col5Len<10
set statistics time off;
The first query, using the structure you have and none of the indexes, takes 207 milliseconds on my desktop machine, 172 milliseconds of CPU time.
The second one, using the calculated columns and the indexes, takes 82 milliseconds total, 78 milliseconds of CPU time.
Since I'm dumping the results into temp tables, that doesn't take into account time to return the data to a screen.
Try something like that, see if it helps.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply