|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Script runs 'forever' and creates huge .LDFfree on drive There are ~ 9 Million rows in table __ACS and there should be < 100,000 rows inserted into __ACSmaster. There are indexes on each of the columns in the Group By - though I suspect they have no effect b/c of the ISNULLs. This script runs for 16+ hours and never completes. The .LDF file starts at 1024 Kb and grows to 17.5 Gb at the time I killed the script. I could set all NULLs to '' with UPDATE __ACS set XXX = '' where XXX is null but would need to do that 9 columns * 9 Million rows = 81 M updates! Questions are: 1) Why does this take so long? 2) Can anything be done in SQL config and/or in the script itself to speed it up? 3) Would it be better to set all cols that contain '' to NULL and remove the ISNULL()s? I thought this would NOT work b/c if A = NULL and B = NULL then A = B is false b/c NULL is an indeterminate value. Any help appreciated. INSERT INTO __ACSmaster(DocNum,Vol,Pg,FilingDate,InstrumentDate,RT,IT_Descr,lu_InstType,IT_ShortDescr) select distinct ISNULL(DocNum,'') AS DocNum, ISNULL(Vol,'') AS Vol, ISNULL(Pg,'') AS Pg, ISNULL(FilingDate,'') AS FilingDate, ISNULL(InstrumentDate,'') AS InstrumentDate, ISNULL(RT,'') AS RT, ISNULL(IT_Descr,'') AS IT_Descr, ISNULL(lu_InstType,0) AS lu_InstType, ISNULL(IT_ShortDescr,'') AS IT_ShortDescr from __ACS group by ISNULL(DocNum,''), ISNULL(Vol,''), ISNULL(Pg,''), ISNULL(FilingDate,''), ISNULL(InstrumentDate,''), ISNULL(RT,''), ISNULL(IT_Descr,''), ISNULL(lu_InstType,0), ISNULL(IT_ShortDescr,'') order by ISNULL(DocNum,''), ISNULL(Vol,''), ISNULL(Pg,''), ISNULL(FilingDate,''), ISNULL(InstrumentDate,''), ISNULL(RT,''), ISNULL(IT_Descr,''), ISNULL(lu_InstType,0), ISNULL(IT_ShortDescr,'') --,ISNULL(Legal,'') GO Couple of things:
ORDERING and GROUPING are probably the two most (computationally) expensive things that SQL can do with a result. Remove the ORDER BY clause, because the order that the results get inserted into the _ACSmaster really shouldn't matter. Additionally, the effect of all those GROUP BY columns is already done by the DISTINCT at the beginning of your query, and since you don't have any aggregates in the query, the GROUP BYs aren't necessary. Additionally, since your query does not have a WHERE condition, it will pull all 9 million rows out of _ACS, run the ISNULL function on all of the columns, and then DISTINCT it (which is expensive), and then insert it. All 9 million rows of _ACS will get inserted into _ACSmaster, which is why you see the log file growing so much. -- Show quote"Sydney Lotterby" wrote: > SQL 2K w/latest SPS, Win2K Adv Server 2gb RAM, dual-core AMD CPU 3400, 80gb > free on drive > > There are ~ 9 Million rows in table __ACS and there should be < 100,000 rows > inserted into __ACSmaster. > There are indexes on each of the columns in the Group By - though I suspect > they have no effect b/c of the ISNULLs. > > This script runs for 16+ hours and never completes. The .LDF file starts at > 1024 Kb and grows to 17.5 Gb at the time I killed the script. > > I could set all NULLs to '' with UPDATE __ACS set XXX = '' where XXX is null > but would need to do that 9 columns * 9 Million rows = 81 M updates! > > Questions are: > 1) Why does this take so long? > 2) Can anything be done in SQL config and/or in the script itself to speed > it up? > 3) Would it be better to set all cols that contain '' to NULL and remove > the ISNULL()s? I thought this would NOT work b/c if A = NULL and B = NULL > then A = B is false b/c NULL is an indeterminate value. > > Any help appreciated. > > INSERT INTO > __ACSmaster(DocNum,Vol,Pg,FilingDate,InstrumentDate,RT,IT_Descr,lu_InstType,IT_ShortDescr) > select distinct ISNULL(DocNum,'') AS DocNum, > ISNULL(Vol,'') AS Vol, > ISNULL(Pg,'') AS Pg, > ISNULL(FilingDate,'') AS FilingDate, > ISNULL(InstrumentDate,'') AS InstrumentDate, > ISNULL(RT,'') AS RT, > ISNULL(IT_Descr,'') AS IT_Descr, > ISNULL(lu_InstType,0) AS lu_InstType, > ISNULL(IT_ShortDescr,'') AS IT_ShortDescr > from __ACS > group by ISNULL(DocNum,''), > ISNULL(Vol,''), > ISNULL(Pg,''), > ISNULL(FilingDate,''), > ISNULL(InstrumentDate,''), > ISNULL(RT,''), > ISNULL(IT_Descr,''), > ISNULL(lu_InstType,0), > ISNULL(IT_ShortDescr,'') > order by ISNULL(DocNum,''), > ISNULL(Vol,''), > ISNULL(Pg,''), > ISNULL(FilingDate,''), > ISNULL(InstrumentDate,''), > ISNULL(RT,''), > ISNULL(IT_Descr,''), > ISNULL(lu_InstType,0), > ISNULL(IT_ShortDescr,'') --,ISNULL(Legal,'') > GO > > > I forgot to thank you for your input.
Show quote "Mark Williams" <MarkWilli***@discussions.microsoft.com> wrote in message news:11050CA8-C55F-4670-A45D-70373EC39812@microsoft.com... > Couple of things: > > ORDERING and GROUPING are probably the two most (computationally) > expensive > things that SQL can do with a result. Remove the ORDER BY clause, because > the > order that the results get inserted into the _ACSmaster really shouldn't > matter. Additionally, the effect of all those GROUP BY columns is already > done by the DISTINCT at the beginning of your query, and since you don't > have > any aggregates in the query, the GROUP BYs aren't necessary. > > Additionally, since your query does not have a WHERE condition, it will > pull > all 9 million rows out of _ACS, run the ISNULL function on all of the > columns, and then DISTINCT it (which is expensive), and then insert it. > All 9 > million rows of _ACS will get inserted into _ACSmaster, which is why you > see > the log file growing so much. > > > -- > "Sydney Lotterby" wrote: > >> SQL 2K w/latest SPS, Win2K Adv Server 2gb RAM, dual-core AMD CPU 3400, >> 80gb >> free on drive >> >> There are ~ 9 Million rows in table __ACS and there should be < 100,000 >> rows >> inserted into __ACSmaster. >> There are indexes on each of the columns in the Group By - though I >> suspect >> they have no effect b/c of the ISNULLs. >> >> This script runs for 16+ hours and never completes. The .LDF file starts >> at >> 1024 Kb and grows to 17.5 Gb at the time I killed the script. >> >> I could set all NULLs to '' with UPDATE __ACS set XXX = '' where XXX is >> null >> but would need to do that 9 columns * 9 Million rows = 81 M updates! >> >> Questions are: >> 1) Why does this take so long? >> 2) Can anything be done in SQL config and/or in the script itself to >> speed >> it up? >> 3) Would it be better to set all cols that contain '' to NULL and remove >> the ISNULL()s? I thought this would NOT work b/c if A = NULL and B = >> NULL >> then A = B is false b/c NULL is an indeterminate value. >> >> Any help appreciated. >> >> INSERT INTO >> __ACSmaster(DocNum,Vol,Pg,FilingDate,InstrumentDate,RT,IT_Descr,lu_InstType,IT_ShortDescr) >> select distinct ISNULL(DocNum,'') AS DocNum, >> ISNULL(Vol,'') AS Vol, >> ISNULL(Pg,'') AS Pg, >> ISNULL(FilingDate,'') AS FilingDate, >> ISNULL(InstrumentDate,'') AS InstrumentDate, >> ISNULL(RT,'') AS RT, >> ISNULL(IT_Descr,'') AS IT_Descr, >> ISNULL(lu_InstType,0) AS lu_InstType, >> ISNULL(IT_ShortDescr,'') AS IT_ShortDescr >> from __ACS >> group by ISNULL(DocNum,''), >> ISNULL(Vol,''), >> ISNULL(Pg,''), >> ISNULL(FilingDate,''), >> ISNULL(InstrumentDate,''), >> ISNULL(RT,''), >> ISNULL(IT_Descr,''), >> ISNULL(lu_InstType,0), >> ISNULL(IT_ShortDescr,'') >> order by ISNULL(DocNum,''), >> ISNULL(Vol,''), >> ISNULL(Pg,''), >> ISNULL(FilingDate,''), >> ISNULL(InstrumentDate,''), >> ISNULL(RT,''), >> ISNULL(IT_Descr,''), >> ISNULL(lu_InstType,0), >> ISNULL(IT_ShortDescr,'') --,ISNULL(Legal,'') >> GO >> >> >> How about using an ALTER TABLE statement to set up a DEFAULT constraint = ' '
on each column in __ACSmaster? Then you wouldn't need all those ISNULL( ) functions. You can always remove the DEFAULT constraints when you're done, if you don't want them there on an ongoing basis... Show quote "Sydney Lotterby" wrote: > SQL 2K w/latest SPS, Win2K Adv Server 2gb RAM, dual-core AMD CPU 3400, 80gb > free on drive > > There are ~ 9 Million rows in table __ACS and there should be < 100,000 rows > inserted into __ACSmaster. > There are indexes on each of the columns in the Group By - though I suspect > they have no effect b/c of the ISNULLs. > > This script runs for 16+ hours and never completes. The .LDF file starts at > 1024 Kb and grows to 17.5 Gb at the time I killed the script. > > I could set all NULLs to '' with UPDATE __ACS set XXX = '' where XXX is null > but would need to do that 9 columns * 9 Million rows = 81 M updates! > > Questions are: > 1) Why does this take so long? > 2) Can anything be done in SQL config and/or in the script itself to speed > it up? > 3) Would it be better to set all cols that contain '' to NULL and remove > the ISNULL()s? I thought this would NOT work b/c if A = NULL and B = NULL > then A = B is false b/c NULL is an indeterminate value. > > Any help appreciated. > > INSERT INTO > __ACSmaster(DocNum,Vol,Pg,FilingDate,InstrumentDate,RT,IT_Descr,lu_InstType,IT_ShortDescr) > select distinct ISNULL(DocNum,'') AS DocNum, > ISNULL(Vol,'') AS Vol, > ISNULL(Pg,'') AS Pg, > ISNULL(FilingDate,'') AS FilingDate, > ISNULL(InstrumentDate,'') AS InstrumentDate, > ISNULL(RT,'') AS RT, > ISNULL(IT_Descr,'') AS IT_Descr, > ISNULL(lu_InstType,0) AS lu_InstType, > ISNULL(IT_ShortDescr,'') AS IT_ShortDescr > from __ACS > group by ISNULL(DocNum,''), > ISNULL(Vol,''), > ISNULL(Pg,''), > ISNULL(FilingDate,''), > ISNULL(InstrumentDate,''), > ISNULL(RT,''), > ISNULL(IT_Descr,''), > ISNULL(lu_InstType,0), > ISNULL(IT_ShortDescr,'') > order by ISNULL(DocNum,''), > ISNULL(Vol,''), > ISNULL(Pg,''), > ISNULL(FilingDate,''), > ISNULL(InstrumentDate,''), > ISNULL(RT,''), > ISNULL(IT_Descr,''), > ISNULL(lu_InstType,0), > ISNULL(IT_ShortDescr,'') --,ISNULL(Legal,'') > GO > > >  |
|||||||||||||||||||||||
Other interesting topics