database performance

hello everyone,
i wasn't sure which sql newsgroup to post this question and server sounds
generic enough
i have a table named RDKCOCUS with 131 columns and over 44k records
sometimes the server will timeout when i select using this statement:
SELECT COUNT(CC.CusId) AS TOTALROWS FROM COCUS AS CC INNER JOIN RDKCOCUS AS
RCC ON CC.CusId = RCC.CusId WHERE (CC.CusId LIKE '%%') AND (CC.Name LIKE
'%%') AND (CC.Post LIKE '%' OR CC.Post IS NULL) AND (CC.Contact LIKE '%' OR
CC.Contact IS NULL) AND (CC.BillCusId LIKE '%%') AND (CC.BillPost LIKE '%'
OR CC.BillPost IS NULL) AND (CC.DateCreate BETWEEN '1/1/1900 12:00:00 AM'
AND '1/4/2006 11:59:59 PM') AND (CC.CusMisc1 LIKE '%' OR CC.CusMisc1 IS
NULL) AND (RCC.Hot LIKE '%') AND CC.CusId NOT IN (SELECT CC.CusId FROM COCUS
AS CC INNER JOIN CONOTITM AS CNI ON CC.NotId = CNI.NotId WHERE
(CNI.DateUpdate BETWEEN '12/5/2005 12:00:00 AM' AND '1/4/2006 11:59:59 PM')
AND (CNI.Des IS NOT NULL) AND (CNI.Des <> '')) AND CC.CusId NOT IN (SELECT
CC.CusId FROM COCUS AS CC INNER JOIN RDKCALLHIST AS RCH ON CC.CusId =
RCH.CusId WHERE (RCH.DateCall BETWEEN '12/5/2005 12:00:00 AM' AND '1/4/2006
11:59:59 PM') AND (RCH.Completed = 1)) AND CC.CusId NOT IN (SELECT CC.CusId
FROM COCUS AS CC INNER JOIN RDKCOCUS AS RCC ON CC.CusId = RCC.CusId WHERE
(RCC.PrefEmpIdSpn IS NOT NULL) AND (RCC.PrefEmpIdSpn <> '')) AND CC.CusId
NOT LIKE '[&]%'
with no traffic and running the statement directly on the server takes about
10 seconds to complete.
does anyone have any information on server performance if i break up the 131
columns into more tables?
someone told me this may help but i need actual performance information
before i start that job
thank you,
abraham luna
I'm surprised you get timeouts. This is a very, very nasty query.
Don't break the tables. Breakup the query into stages. Work on creating
cleaner code.
d.
"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:%23bcuT8wEGHA.868@.TK2MSFTNGP10.phx.gbl...
> hello everyone,
> i wasn't sure which sql newsgroup to post this question and server sounds
> generic enough
> i have a table named RDKCOCUS with 131 columns and over 44k records
> sometimes the server will timeout when i select using this statement:
> SELECT COUNT(CC.CusId) AS TOTALROWS FROM COCUS AS CC INNER JOIN RDKCOCUS
AS
> RCC ON CC.CusId = RCC.CusId WHERE (CC.CusId LIKE '%%') AND (CC.Name LIKE
> '%%') AND (CC.Post LIKE '%' OR CC.Post IS NULL) AND (CC.Contact LIKE '%'
OR
> CC.Contact IS NULL) AND (CC.BillCusId LIKE '%%') AND (CC.BillPost LIKE '%'
> OR CC.BillPost IS NULL) AND (CC.DateCreate BETWEEN '1/1/1900 12:00:00 AM'
> AND '1/4/2006 11:59:59 PM') AND (CC.CusMisc1 LIKE '%' OR CC.CusMisc1 IS
> NULL) AND (RCC.Hot LIKE '%') AND CC.CusId NOT IN (SELECT CC.CusId FROM
COCUS
> AS CC INNER JOIN CONOTITM AS CNI ON CC.NotId = CNI.NotId WHERE
> (CNI.DateUpdate BETWEEN '12/5/2005 12:00:00 AM' AND '1/4/2006 11:59:59
PM')
> AND (CNI.Des IS NOT NULL) AND (CNI.Des <> '')) AND CC.CusId NOT IN (SELECT
> CC.CusId FROM COCUS AS CC INNER JOIN RDKCALLHIST AS RCH ON CC.CusId =
> RCH.CusId WHERE (RCH.DateCall BETWEEN '12/5/2005 12:00:00 AM' AND
'1/4/2006
> 11:59:59 PM') AND (RCH.Completed = 1)) AND CC.CusId NOT IN (SELECT
CC.CusId
> FROM COCUS AS CC INNER JOIN RDKCOCUS AS RCC ON CC.CusId = RCC.CusId WHERE
> (RCC.PrefEmpIdSpn IS NOT NULL) AND (RCC.PrefEmpIdSpn <> '')) AND CC.CusId
> NOT LIKE '[&]%'
> with no traffic and running the statement directly on the server takes
about
> 10 seconds to complete.
> does anyone have any information on server performance if i break up the
131
> columns into more tables?
> someone told me this may help but i need actual performance information
> before i start that job
> thank you,
> abraham luna
>
|||"Abraham Andres Luna" <abe@.rdk.com> wrote in message
news:%23bcuT8wEGHA.868@.TK2MSFTNGP10.phx.gbl...
> hello everyone,
> i wasn't sure which sql newsgroup to post this question and server sounds
> generic enough
> i have a table named RDKCOCUS with 131 columns and over 44k records
> sometimes the server will timeout when i select using this statement:
> SELECT COUNT(CC.CusId) AS TOTALROWS FROM COCUS AS CC INNER JOIN RDKCOCUS
> AS RCC ON CC.CusId = RCC.CusId WHERE (CC.CusId LIKE '%%') AND (CC.Name
> LIKE '%%') AND (CC.Post LIKE '%' OR CC.Post IS NULL) AND (CC.Contact LIKE
> '%' OR CC.Contact IS NULL) AND (CC.BillCusId LIKE '%%') AND (CC.BillPost
> LIKE '%' OR CC.BillPost IS NULL) AND (CC.DateCreate BETWEEN '1/1/1900
> 12:00:00 AM' AND '1/4/2006 11:59:59 PM') AND (CC.CusMisc1 LIKE '%' OR
> CC.CusMisc1 IS NULL) AND (RCC.Hot LIKE '%') AND CC.CusId NOT IN (SELECT
> CC.CusId FROM COCUS AS CC INNER JOIN CONOTITM AS CNI ON CC.NotId =
> CNI.NotId WHERE (CNI.DateUpdate BETWEEN '12/5/2005 12:00:00 AM' AND
> '1/4/2006 11:59:59 PM') AND (CNI.Des IS NOT NULL) AND (CNI.Des <> '')) AND
> CC.CusId NOT IN (SELECT CC.CusId FROM COCUS AS CC INNER JOIN RDKCALLHIST
> AS RCH ON CC.CusId = RCH.CusId WHERE (RCH.DateCall BETWEEN '12/5/2005
> 12:00:00 AM' AND '1/4/2006 11:59:59 PM') AND (RCH.Completed = 1)) AND
> CC.CusId NOT IN (SELECT CC.CusId FROM COCUS AS CC INNER JOIN RDKCOCUS AS
> RCC ON CC.CusId = RCC.CusId WHERE (RCC.PrefEmpIdSpn IS NOT NULL) AND
> (RCC.PrefEmpIdSpn <> '')) AND CC.CusId NOT LIKE '[&]%'
> with no traffic and running the statement directly on the server takes
> about 10 seconds to complete.
> does anyone have any information on server performance if i break up the
> 131 columns into more tables?
>
Don't do that for performance reasons (there may well be design reasons to
do it). For performance, proper indexing can do just as well. For
instance, of your 133 columns you only use:
(CusId, Hot, PrefEmpIdSpn, PrefEmpIdSpn). And CusID is used in the join.
So I would start with an index on these.
create index ix_CustIDxxx on RDKCOCUS(CusId, Hot, PrefEmpIdSpn,
PrefEmpIdSpn)
This will let SQL aviod your large, wide table, and hit only a small, narrow
index structure. If you are on SQL 2005 you might just make this an index
on CusID with INCLUDED COLUMNS (Hot, PrefEmpIdSpn, PrefEmpIdSpn). It will
serve the same purpose, but will be smaller.
David
|||"d" <d@.d.com> wrote in message news:uAGvf.451$ww5.34@.trnddc01...
> I'm surprised you get timeouts. This is a very, very nasty query.
> Don't break the tables. Breakup the query into stages. Work on creating
> cleaner code.
> d.
>
Hmm. The code's not too bad. Obviously "autogenerated", but it's pretty
clean. Here it is formatted reasonably:
SELECT
COUNT(CC.CusId) AS TOTALROWS
FROM COCUS AS CC
INNER JOIN RDKCOCUS AS RCC
ON CC.CusId = RCC.CusId
WHERE (CC.CusId LIKE '%%')
AND (CC.Name LIKE '%%')
AND (CC.Post LIKE '%' OR CC.Post IS NULL)
AND (CC.Contact LIKE '%' OR CC.Contact IS NULL)
AND (CC.BillCusId LIKE '%%')
AND (CC.BillPost LIKE '%' OR CC.BillPost IS NULL)
AND (CC.DateCreate BETWEEN '1/1/1900 12:00:00 AM' AND '1/4/2006 11:59:59
PM')
AND (CC.CusMisc1 LIKE '%' OR CC.CusMisc1 IS NULL)
AND (RCC.Hot LIKE '%')
AND CC.CusId NOT IN
(
SELECT CC.CusId
FROM COCUS AS CC
INNER JOIN CONOTITM AS CNI
ON CC.NotId = CNI.NotId
WHERE (CNI.DateUpdate BETWEEN '12/5/2005 12:00:00 AM' AND '1/4/2006
11:59:59 PM')
AND (CNI.Des IS NOT NULL)
AND (CNI.Des <> '')
)
AND CC.CusId NOT IN
(
SELECT CC.CusId
FROM COCUS AS CC
INNER JOIN RDKCALLHIST AS RCH
ON CC.CusId = RCH.CusId
WHERE (RCH.DateCall BETWEEN '12/5/2005 12:00:00 AM' AND '1/4/2006
11:59:59 PM')
AND (RCH.Completed = 1)
)
AND CC.CusId NOT IN
(
SELECT CC.CusId
FROM COCUS AS CC
INNER JOIN RDKCOCUS AS RCC
ON CC.CusId = RCC.CusId
WHERE (RCC.PrefEmpIdSpn IS NOT NULL)
AND (RCC.PrefEmpIdSpn <> '')
)
AND CC.CusId NOT LIKE '[&]%'
Breaking a query up into stages assumes that you "know better" than SQL how
to execute the query, and you can often shoot yourself in the foot.
David