HELP! Website is slammed!

I don't even know if this is on topic or not...I post regularly here and it
does involve SQL2000 so here goes.
I have a view(listed below) I'm selecting from it involves about 10 tables
and I'm filtering down to around 60 rows in the result set like this:
select * from dbo.vwb_webeventlist where blnwebexpired=0 and
blnwebavailable=1 and lngeventtypefk=63
This has been working fine in place for months. Nothing that we can
determine has changed. The query above runs from query analyzer in less
than a second from my workstation with the same connection settings as we
are running from our app. The app is executing the same query as follows:
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.cn;
cmd.CommandText = "select * from vwb_webeventlist where blnwebexpired=0
and blnwebavailable=1 and lngeventtypefk=63";
cmd.CommandType = CommandType.Text;
this.cn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
This afternoon this was taking more than 15 seconds to run so our website
started timing out on everyone. After rebooting the SQL Server box the time
dropped to around 5 seconds which is still obviously unacceptable. I'm at a
loss as to what could cause this. The query still executes quickly from
query analyzer. Our website is basically unusable at the moment and it is
getting much hotter in here....
CREATE VIEW [dbo].[vwb_WebEventList]
AS
SELECT TOP 100 PERCENT evt.lngEventPK, evtm.lngEventMeetingPK,
dbo.fnbTicketsLeft(evtm.lngEventMeetingPK) AS intTicketsLeft,
evttyp.strType AS strEventType, evttyp.strTicketMID,
evttyp.strHotelMID, evt.strEventName AS strLocation, evt.strEventState AS
strState,
evt.dtmStartDate, evt.dtmEndDate, evtweb.dtmWebStart,
evtweb.dtmWebEnd, evt.lngEventTypeFK, price.curPrice AS curMeetingPrice,
convfee.curPrice AS curConvenienceFee,
evttyp.blnWebExpired, evt.blnWebAvailable, evt.blnUnderConstruction,
evt.lngHostMemFK, evt.strEventName,
evtweb.dtmBS, evt.strMID AS strFRMID,
faccmp.strCompanyName AS strFacility, evtwebm.strSpeaker, evt.curSingRate AS
cursingrateR,
evt.curDoubRate AS curdoubrateR, latefee.curPrice AS
curLateFee, evt.curCancelFee AS curCancelFeer, singfee.curPrice AS
curSingRate,
doubfee.curPrice AS curDoubRate, canxfee.curPrice AS
curCancelFee, evt.strRegWhere, evt.dtmRegBeginTime, evt.dtmRegEndTime,
evt.strJobCode
FROM dbo.tblBEvents AS evt INNER JOIN
dbo.tblBEventMeetings AS evtm ON
evtm.lngEventMeetingPK = evt.lngEventMeetingFK INNER JOIN
dbo.tblBEventTypes AS evttyp ON evttyp.lngEventTypePK
= evt.lngEventTypeFK LEFT OUTER JOIN
dbo.tblBMeetingRooms AS evtmr ON
evtmr.lngEventMeetingFK = evt.lngEventMeetingFK LEFT OUTER JOIN
dbo.tblBFacilities AS fac ON fac.lngFacilitiesPK = evtmr.lngFacilityFK LEFT OUTER JOIN
dbo.tblMCompany AS faccmp ON faccmp.lngMemNumberPK = fac.lngFacilityMemFK INNER JOIN
dbo.tblBWebEvents AS evtweb ON evtweb.lngEventFK = evt.lngEventPK INNER JOIN
dbo.tblBWebEventMeetings AS evtwebm ON
evtwebm.lngEventMeetingFK = evtm.lngEventMeetingPK LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS price ON
price.lngEventMeetingFK = evtm.lngEventMeetingPK AND price.intPriceTypeFK = 1 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS singfee ON
singfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
singfee.intPriceTypeFK = 2 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS doubfee ON
doubfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
doubfee.intPriceTypeFK = 3 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS convfee ON
convfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
convfee.intPriceTypeFK = 6 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS canxfee ON
canxfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
canxfee.intPriceTypeFK = 8 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS latefee ON
latefee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
latefee.intPriceTypeFK = 7
ORDER BY price.curPrice DESC, fac.strState, fac.strLocation,
evt.dtmStartDate"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23Pvfj27cGHA.2188@.TK2MSFTNGP04.phx.gbl...
> I don't even know if this is on topic or not...I post regularly here and
it
> does involve SQL2000 so here goes.
>
> I have a view(listed below) I'm selecting from it involves about 10 tables
> and I'm filtering down to around 60 rows in the result set like this:
> select * from dbo.vwb_webeventlist where blnwebexpired=0 and
> blnwebavailable=1 and lngeventtypefk=63
Well, first I'd get rid of the select *. Return only the rows that you
need.
But that obviously doesn't explain the sudden difference in performance
you're seeing.
I'd also make sure that you run update statistics on the tables involved.
And just to be extra careful, make sure your switch or something didn't
autonegotiate to something weird like 10Mb/sec half-duplex.
> This has been working fine in place for months. Nothing that we can
> determine has changed. The query above runs from query analyzer in less
> than a second from my workstation with the same connection settings as we
> are running from our app. The app is executing the same query as follows:
> SqlCommand cmd = new SqlCommand();
> cmd.Connection = this.cn;
> cmd.CommandText = "select * from vwb_webeventlist where blnwebexpired=0
> and blnwebavailable=1 and lngeventtypefk=63";
> cmd.CommandType = CommandType.Text;
> this.cn.Open();
> SqlDataReader rdr = cmd.ExecuteReader();
> This afternoon this was taking more than 15 seconds to run so our website
> started timing out on everyone. After rebooting the SQL Server box the
time
> dropped to around 5 seconds which is still obviously unacceptable. I'm at
a
> loss as to what could cause this. The query still executes quickly from
> query analyzer. Our website is basically unusable at the moment and it is
> getting much hotter in here....
>
>
>
>
> CREATE VIEW [dbo].[vwb_WebEventList]
> AS
> SELECT TOP 100 PERCENT evt.lngEventPK, evtm.lngEventMeetingPK,
> dbo.fnbTicketsLeft(evtm.lngEventMeetingPK) AS intTicketsLeft,
> evttyp.strType AS strEventType, evttyp.strTicketMID,
> evttyp.strHotelMID, evt.strEventName AS strLocation, evt.strEventState AS
> strState,
> evt.dtmStartDate, evt.dtmEndDate,
evtweb.dtmWebStart,
> evtweb.dtmWebEnd, evt.lngEventTypeFK, price.curPrice AS curMeetingPrice,
> convfee.curPrice AS curConvenienceFee,
> evttyp.blnWebExpired, evt.blnWebAvailable, evt.blnUnderConstruction,
> evt.lngHostMemFK, evt.strEventName,
> evtweb.dtmBS, evt.strMID AS strFRMID,
> faccmp.strCompanyName AS strFacility, evtwebm.strSpeaker, evt.curSingRate
AS
> cursingrateR,
> evt.curDoubRate AS curdoubrateR, latefee.curPrice AS
> curLateFee, evt.curCancelFee AS curCancelFeer, singfee.curPrice AS
> curSingRate,
> doubfee.curPrice AS curDoubRate, canxfee.curPrice AS
> curCancelFee, evt.strRegWhere, evt.dtmRegBeginTime, evt.dtmRegEndTime,
> evt.strJobCode
> FROM dbo.tblBEvents AS evt INNER JOIN
> dbo.tblBEventMeetings AS evtm ON
> evtm.lngEventMeetingPK = evt.lngEventMeetingFK INNER JOIN
> dbo.tblBEventTypes AS evttyp ON
evttyp.lngEventTypePK
> = evt.lngEventTypeFK LEFT OUTER JOIN
> dbo.tblBMeetingRooms AS evtmr ON
> evtmr.lngEventMeetingFK = evt.lngEventMeetingFK LEFT OUTER JOIN
> dbo.tblBFacilities AS fac ON fac.lngFacilitiesPK => evtmr.lngFacilityFK LEFT OUTER JOIN
> dbo.tblMCompany AS faccmp ON faccmp.lngMemNumberPK => fac.lngFacilityMemFK INNER JOIN
> dbo.tblBWebEvents AS evtweb ON evtweb.lngEventFK => evt.lngEventPK INNER JOIN
> dbo.tblBWebEventMeetings AS evtwebm ON
> evtwebm.lngEventMeetingFK = evtm.lngEventMeetingPK LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS price ON
> price.lngEventMeetingFK = evtm.lngEventMeetingPK AND price.intPriceTypeFK
=> 1 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS singfee ON
> singfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> singfee.intPriceTypeFK = 2 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS doubfee ON
> doubfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> doubfee.intPriceTypeFK = 3 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS convfee ON
> convfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> convfee.intPriceTypeFK = 6 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS canxfee ON
> canxfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> canxfee.intPriceTypeFK = 8 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS latefee ON
> latefee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> latefee.intPriceTypeFK = 7
> ORDER BY price.curPrice DESC, fac.strState, fac.strLocation,
> evt.dtmStartDate
>
>|||Thanks for responding....
I understand the "select *" issue. I would NEVER code that unless I was
investigating in query analyzer or something. I did not write the code in
question, but I know the particular view was created specifically for this
purpose ( a great waste ) and this purpose only (so why not just code the
query)...
This was the strangest thing. Changing the server in my connection string
to another server and the .NET app ran as it should. Point it back at
production and wham...
This morning I backed up and restored the production database and oddly
enough, now it seems to be fine. Go figure.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eBaoD38cGHA.3348@.TK2MSFTNGP03.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23Pvfj27cGHA.2188@.TK2MSFTNGP04.phx.gbl...
>> I don't even know if this is on topic or not...I post regularly here and
> it
>> does involve SQL2000 so here goes.
>>
>> I have a view(listed below) I'm selecting from it involves about 10
>> tables
>> and I'm filtering down to around 60 rows in the result set like this:
>> select * from dbo.vwb_webeventlist where blnwebexpired=0 and
>> blnwebavailable=1 and lngeventtypefk=63
> Well, first I'd get rid of the select *. Return only the rows that you
> need.
> But that obviously doesn't explain the sudden difference in performance
> you're seeing.
> I'd also make sure that you run update statistics on the tables involved.
> And just to be extra careful, make sure your switch or something didn't
> autonegotiate to something weird like 10Mb/sec half-duplex.
>
>> This has been working fine in place for months. Nothing that we can
>> determine has changed. The query above runs from query analyzer in less
>> than a second from my workstation with the same connection settings as we
>> are running from our app. The app is executing the same query as
>> follows:
>> SqlCommand cmd = new SqlCommand();
>> cmd.Connection = this.cn;
>> cmd.CommandText = "select * from vwb_webeventlist where
>> blnwebexpired=0
>> and blnwebavailable=1 and lngeventtypefk=63";
>> cmd.CommandType = CommandType.Text;
>> this.cn.Open();
>> SqlDataReader rdr = cmd.ExecuteReader();
>> This afternoon this was taking more than 15 seconds to run so our website
>> started timing out on everyone. After rebooting the SQL Server box the
> time
>> dropped to around 5 seconds which is still obviously unacceptable. I'm
>> at
> a
>> loss as to what could cause this. The query still executes quickly from
>> query analyzer. Our website is basically unusable at the moment and it
>> is
>> getting much hotter in here....
>>
>>
>>
>>
>> CREATE VIEW [dbo].[vwb_WebEventList]
>> AS
>> SELECT TOP 100 PERCENT evt.lngEventPK, evtm.lngEventMeetingPK,
>> dbo.fnbTicketsLeft(evtm.lngEventMeetingPK) AS intTicketsLeft,
>> evttyp.strType AS strEventType,
>> evttyp.strTicketMID,
>> evttyp.strHotelMID, evt.strEventName AS strLocation, evt.strEventState AS
>> strState,
>> evt.dtmStartDate, evt.dtmEndDate,
> evtweb.dtmWebStart,
>> evtweb.dtmWebEnd, evt.lngEventTypeFK, price.curPrice AS curMeetingPrice,
>> convfee.curPrice AS curConvenienceFee,
>> evttyp.blnWebExpired, evt.blnWebAvailable, evt.blnUnderConstruction,
>> evt.lngHostMemFK, evt.strEventName,
>> evtweb.dtmBS, evt.strMID AS strFRMID,
>> faccmp.strCompanyName AS strFacility, evtwebm.strSpeaker, evt.curSingRate
> AS
>> cursingrateR,
>> evt.curDoubRate AS curdoubrateR, latefee.curPrice
>> AS
>> curLateFee, evt.curCancelFee AS curCancelFeer, singfee.curPrice AS
>> curSingRate,
>> doubfee.curPrice AS curDoubRate, canxfee.curPrice
>> AS
>> curCancelFee, evt.strRegWhere, evt.dtmRegBeginTime, evt.dtmRegEndTime,
>> evt.strJobCode
>> FROM dbo.tblBEvents AS evt INNER JOIN
>> dbo.tblBEventMeetings AS evtm ON
>> evtm.lngEventMeetingPK = evt.lngEventMeetingFK INNER JOIN
>> dbo.tblBEventTypes AS evttyp ON
> evttyp.lngEventTypePK
>> = evt.lngEventTypeFK LEFT OUTER JOIN
>> dbo.tblBMeetingRooms AS evtmr ON
>> evtmr.lngEventMeetingFK = evt.lngEventMeetingFK LEFT OUTER JOIN
>> dbo.tblBFacilities AS fac ON fac.lngFacilitiesPK =>> evtmr.lngFacilityFK LEFT OUTER JOIN
>> dbo.tblMCompany AS faccmp ON faccmp.lngMemNumberPK
>> =>> fac.lngFacilityMemFK INNER JOIN
>> dbo.tblBWebEvents AS evtweb ON evtweb.lngEventFK =>> evt.lngEventPK INNER JOIN
>> dbo.tblBWebEventMeetings AS evtwebm ON
>> evtwebm.lngEventMeetingFK = evtm.lngEventMeetingPK LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS price ON
>> price.lngEventMeetingFK = evtm.lngEventMeetingPK AND price.intPriceTypeFK
> =>> 1 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS singfee ON
>> singfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> singfee.intPriceTypeFK = 2 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS doubfee ON
>> doubfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> doubfee.intPriceTypeFK = 3 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS convfee ON
>> convfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> convfee.intPriceTypeFK = 6 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS canxfee ON
>> canxfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> canxfee.intPriceTypeFK = 8 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS latefee ON
>> latefee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> latefee.intPriceTypeFK = 7
>> ORDER BY price.curPrice DESC, fac.strState, fac.strLocation,
>> evt.dtmStartDate
>>
>