| Author |
Topic |
|
RoniR
Starting Member
16 Posts |
 Posted - 2009-07-05 : 10:46:35
|
| i wrote this query that searched through tables containing more theb a million record and it takes 3 hours to finish...how can i re write it..here i am looking up international calls from a CDR ..table that contains for then 1 million record...use aprilselect count (Distinct NorthAmerica)-1 AS NORTHAMERICA,SUM(NorthAmericaDebirate1),SUM(NorthAmericachargeduration),count (Distinct Guinea)-1 AS Guinia,sum(GuineaDebirate1),sum(Guineachargeduration),count (Distinct uk)-1 as uk, sum (ukdebirate),sum(ukChargeDuration),count(Distinct gambia)-1 as gambia,sum(Gambiadebitrate),sum(GambiaChargeDuration),count(Distinct australia) -1 as australia,sum(AustraliaDebirate1),sum(Australiachargeduration),count(Distinct liberia)-1 as liberia,sum(Liberiadebirate),sum(Liberiachargeduration),count(Distinct nigeria)-1 as nigeria,sum(Nigeriadebitrate),sum(nigeriachargeduration),count(Distinct netherlands)-1 as holland, sum(Netherlandsdebitrate),sum(Netherlandschargeduration),count(Distinct lebanon)-1 as leb,sum(lebanondebitrate),sum(Lebanonchargeduration),count (Distinct germany)-1 as germany, sum(Germanydebitrate),sum(Germanychargeduration),count(Distinct senegal)-1 as senegal ,sum(Senegaldebitrate),sum(Senegalchargeduration),count (Distinct IvoryCoast)-1 as ivorycoast,sum(IvoryCoastdebitrate1),sum(IvoryCoastchargeduration),count(Distinct mali)-1 as mali,sum(malidebitrate),sum(malichargeduration),count(Distinct ghana)-1 as ghana,sum(ghanadebitrate),sum(ghanachargeduration),count(Distinct belgium) -1 as belgium, sum(BelgiumDEBITRATE),sum(BelgiumCHARGEDURATION) from (select case when substring(called,1,3) like ('001') then accnum else 0 end as NorthAmerica,case when substring(called,1,3) like ('001') then debitrate1 else 0 END as NorthAmericaDebirate1,case when substring(called,1,3) like ('001') then chargeduration else 0 END as NorthAmericachargeduration,case when substring(called,1,5) like ('00224') then accnum else 0 end as Guinea,case when substring(called,1,5) like ('00224') then debitrate1 else 0 END as GuineaDebirate1,case when substring(called,1,5) like ('00224') then chargeduration else 0 END as Guineachargeduration,case when substring(called,1,4) like ('0044') then accnum else 0 end as Uk,case when substring(called,1,4) like ('0044') then debitrate1 else 0 end as ukdebirate,case when substring(called,1,4) like ('0044') then chargeduration else 0 end as ukChargeDuration,case when substring(called,1,5) like ('00220') then accnum else 0 end as Gambia,case when substring(called,1,5) like ('00220') then debitrate1 else 0 END as Gambiadebitrate,case when substring(called,1,5) like ('00220') then chargeduration else 0 END as GambiaChargeDuration,case when substring(called,1,4) like ('0061') then accnum else 0 end as Australia,case when substring(called,1,4) like ('0061') then debitrate1 else 0 END as AustraliaDebirate1,case when substring(called,1,4) like ('0061') then chargeduration else 0 END as Australiachargeduration,case when substring(called,1,5) like ('00231') then accnum else 0 end as Liberia,case when substring(called,1,5) like ('00231') then debitrate1 else 0 END as Liberiadebirate,case when substring(called,1,5) like ('00231') then chargeduration else 0 END as Liberiachargeduration,case when substring(called,1,5) like ('00234') then accnum else 0 end as Nigeria,case when substring(called,1,5) like ('00234') then debitrate1 else 0 END as Nigeriadebitrate,case when substring(called,1,5) like ('00234') then chargeduration else 0 END as Nigeriachargeduration,case when substring(called,1,4) like ('0031') then accnum else 0 end as Netherlands,case when substring(called,1,4) like ('0031') then debitrate1 else 0 END as Netherlandsdebitrate,case when substring(called,1,4) like ('0031') then chargeduration else 0 END as Netherlandschargeduration,case when substring(called,1,5) like ('00961') then accnum else 0 end as Lebanon,case when substring(called,1,5) like ('00961') then debitrate1 else 0 END as Lebanondebitrate,case when substring(called,1,5) like ('00961') then chargeduration else 0 END as Lebanonchargeduration,case when substring(called,1,4) like ('0049') then accnum else 0 end as Germany,case when substring(called,1,4) like ('0049') then debitrate1 else 0 END as Germanydebitrate,case when substring(called,1,4) like ('0049') then chargeduration else 0 END as Germanychargeduration,case when substring(called,1,5) like ('00221') then accnum else 0 end as Senegal,case when substring(called,1,5) like ('00221') then debitrate1 else 0 END as Senegaldebitrate,case when substring(called,1,5) like ('00221') then chargeduration else 0 END as Senegalchargeduration,case when substring(called,1,5) like ('00225') then accnum else 0 end as IvoryCoast,case when substring(called,1,5) like ('00225') then debitrate1 else 0 END as IvoryCoastdebitrate1,case when substring(called,1,5) like ('00225') then chargeduration else 0 END as IvoryCoastchargeduration,case when substring(called,1,5) like ('00223') then accnum else 0 end as Mali,case when substring(called,1,5) like ('00223') then debitrate1 else 0 END as Malidebitrate,case when substring(called,1,5) like ('00223') then chargeduration else 0 END as Malichargeduration,case when substring(called,1,5) like ('00233') then accnum else 0 end as Ghana,case when substring(called,1,5) like ('00233') then DEBITRATE1 ELSE 0 END AS ghanaDEBITRATE,case when substring(called,1,5) like ('00233') then CHARGEDURATION ELSE 0 END AS ghanaCHARGEDURATION,case when substring(called,1,4) like ('0032') then accnum else 0 end as Belgium,case when substring(called,1,4) like ('0032') then DEBITRATE1 ELSE 0 END AS BelgiumDEBITRATE,case when substring(called,1,4) like ('0032') then CHARGEDURATION ELSE 0 END AS BelgiumCHARGEDURATIONfrom april..aprwhere type in (16,20)) there this select gets me all the info i want..but it takes too much time...i could have done it on a day to day table (cdr20090401) but i chose to do that on the big table..this tabke april..apr contains all the phone calls done in the month of april..so u can imagine how many records it contains..how can i rewrite this query..would it be better if i dealt with it in another way.thanks for any reply |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-05 : 12:43:42
|
Try thisSELECT COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 3) = '001' THEN AccNum ELSE NULL END) AS NorthAmerica, SUM(CASE WHEN SUBSTRING(Called, 1, 3) = '001' THEN DebitRate1 ELSE 0 END) AS NorthAmericaDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 3) = '001' THEN ChargeDuration ELSE 0 END) AS NorthAmericaChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00224' THEN AccNum ELSE NULL END) AS Guinea, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00224' THEN DebitRate1 ELSE 0 END) AS GuineaDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00224' THEN ChargeDuration ELSE 0 END) AS GuineaChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 4) = '0044' THEN AccNum ELSE NULL END) AS Uk, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0044' THEN DebitRate1 ELSE 0 END) AS UkDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0044' THEN ChargeDuration ELSE 0 END) AS UkChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00220' THEN AccNum ELSE NULL END) AS Gambia, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00220' THEN DebitRate1 ELSE 0 END) AS GambiaDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00220' THEN ChargeDuration ELSE 0 END) AS GambiaChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 4) = '0061' THEN AccNum ELSE NULL END) AS Australia, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0061' THEN DebitRate1 ELSE 0 END) AS AustraliaDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0061' THEN ChargeDuration ELSE 0 END) AS AustraliaChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00231' THEN AccNum ELSE NULL END) AS Liberia, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00231' THEN DebitRate1 ELSE 0 END) AS LiberiaDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00231' THEN ChargeDuration ELSE 0 END) AS LiberiaChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00234' THEN AccNum ELSE NULL END) AS Nigeria, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00234' THEN DebitRate1 ELSE 0 END) AS NigeriaDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00234' THEN ChargeDuration ELSE 0 END) AS NigeriaChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 4) = '0031' THEN AccNum ELSE NULL END) AS Netherlands, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0031' THEN DebitRate1 ELSE 0 END) AS NetherlandsDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0031' THEN ChargeDuration ELSE 0 END) AS NetherlandsChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00961' THEN AccNum ELSE NULL END) AS Lebanon, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00961' THEN DebitRate1 ELSE 0 END) AS LebanonDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00961' THEN ChargeDuration ELSE 0 END) AS LebanonChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 4) = '0049' THEN AccNum ELSE NULL END) AS Germany, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0049' THEN DebitRate1 ELSE 0 END) AS GermanyDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0049' THEN ChargeDuration ELSE 0 END) AS GermanyChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00221' THEN AccNum ELSE NULL END) AS Senegal, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00221' THEN DebitRate1 ELSE 0 END) AS SenegalDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00221' THEN ChargeDuration ELSE 0 END) AS SenegalChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00225' THEN AccNum ELSE NULL END) AS IvoryCoast, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00225' THEN DebitRate1 ELSE 0 END) AS IvoryCoastDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00225' THEN ChargeDuration ELSE 0 END) AS IvoryCoastChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00223' THEN AccNum ELSE NULL END) AS Mali, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00223' THEN DebitRate1 ELSE 0 END) AS MaliDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00223' THEN ChargeDuration ELSE 0 END) AS MaliChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 5) = '00233' THEN AccNum ELSE NULL END) AS Ghana, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00233' THEN DebitRate1 ELSE 0 END) AS GhanaDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 5) = '00233' THEN ChargeDuration ELSE 0 END) AS GhanaChargeDuration, COUNT(DISTINCT CASE WHEN SUBSTRING(Called, 1, 4) = '0032' THEN AccNum ELSE NULL END) AS Belgium, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0032' THEN DebitRate1 ELSE 0 END) AS BelgiumDebitRate, SUM(CASE WHEN SUBSTRING(Called, 1, 4) = '0032' THEN ChargeDuration ELSE 0 END) AS BelgiumChargeDuration,FROM April..AprWHERE [Type] IN (16, 20) AND ( SUBSTRING(Called, 1, 3) = '001' OR SUBSTRING(Called, 1, 4) IN ('0044', '0061', '0031', '0049', '0032') OR SUBSTRING(Called, 1, 5) IN ('00224', '00220', '00231', '00234', '00961', '00221', '00225', '00223', '00233') )
Microsoft SQL Server MVPN 56°04'39.26"E 12°55'05.63" |
 |
|
|
saran_d28
Starting Member
36 Posts |
Posted - 2009-07-06 : 02:39:03
|
| Thats Correct peso,In the where clause, we can use "like" instead of substring function. It will increase the performance.WHERE [Type] IN (16, 20) AND ( Called like '001%'or Called like '0044%', or called like '0061%'or called like '0031%'or called like '0049%' or called like '0032%' )--Saravanan |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-06 : 03:42:41
|
No. LIKE will not increase performance.In this case, LIKE will make any index on Called column useless.The SUBSTRING will however make it possible for a HASH MATCH since there are a million records, and SUBSTRING forces a fixed length.
Microsoft SQL Server MVPN 56°04'39.26"E 12°55'05.63" |
|
|
|
RoniR
Starting Member
16 Posts |
Posted - 2009-07-09 : 16:02:55
|
| cheers guys peso i loved it..it took 8 minutes...while mine took 2 hours...talk about time is money:Dgreat..trying to learn something out from itthanks in advance |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-07-09 : 16:18:30
|
8 minutes only? From 2 hours?About 15 times faster?Seems like a keeper to me...
N 56°04'39.26"E 12°55'05.63" |
|
|
|
|
|
|
Query takes too much time