| Author |
Topic |
|
datzent83
Starting Member
5 Posts |
 Posted - 2007-01-09 : 13:36:48
|
| Hello All,It seems the following queries are causing my DB connections to time out, but I cant seem to figure out why. These queries reside within my xml.config file. The connection to the DB is fine and live. I get timeout errors every few hours or so.Can anyone take a look at the queries which I pasted below and tell me why I keep getting timeout errors? PLEASE, I need all the help I can get. <query name="Products" rowElementName="Product"> <sql> <![CDATA[ SELECT p.*, pv.VariantID, pv.name VariantName, pv.Price, pv.Description VariantDescription, isnull(pv.SalePrice, 0) SalePrice, isnull(SkuSuffix, '') SkuSuffix, pv.Dimensions, pv.Weight, isnull(pv.Points, 0) Points, sp.name SalesPromptName, isnull(e.Price, 0) ExtendedPrice FROM Product p join productvariant pv on p.ProductID = pv.ProductID join SalesPrompt sp on p.SalesPromptID = sp.SalesPromptID left join ExtendedPrice e on pv.VariantID=e.VariantID and e.CustomerLevelID=@CustomerLevelID WHERE p.ProductID = @ProductID and p.Deleted = 0 and pv.Deleted = 0 and p.Published = 1 and pv.Published = 1 ORDER BY p.ProductID, pv.DisplayOrder, pv.Name ]]> </sql> <queryparam paramname="@CustomerLevelID" paramtype="runtime" requestparamname="CustomerLevelID" sqlDataType="int" defvalue="0" validationpattern="" /> <queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" /> </query> <query name="Ratings" rowElementName="Rating"> <sql> <![CDATA[ SELECT ProductID, CAST ( AVG(CAST(Rating AS decimal)) as decimal (10,2)) as Rating FROM Rating WHERE ProductID = @ProductID Group By ProductID ORDER BY ProductID ]]> </sql> <queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" /> </query> <query name="TotalRatings" rowElementName="TotalRating"> <sql> <![CDATA[ SELECT ProductID, Count(Rating) as TotalRating FROM Rating WHERE ProductID = @ProductID Group By ProductID ORDER BY ProductID ]]> </sql> <queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" /> </query> <query name="popup" rowElementName="popit"> <sql> <![CDATA[ SELECT ProductID as rateID FROM Product WHERE ProductID = @ProductID Group By ProductID ]]> </sql> <queryparam paramname="@ProductID" paramtype="request" requestparamname="ProductID" sqlDataType="int" defvalue="0" validationpattern="^\d{1,10}$" /> </query> |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 14:12:40
|
| For the tables that are involved in these queries, what indexes do you have?Tara Kizer |
 |
|
|
datzent83
Starting Member
5 Posts |
Posted - 2007-01-09 : 14:19:57
|
| I am not sure I have any indexes for any table. How do I check if I have any indexes? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 14:28:32
|
| Well that would be the problem then. What version of SQL Server are you using?Tara Kizer |
|
|
|
datzent83
Starting Member
5 Posts |
Posted - 2007-01-09 : 16:22:20
|
| I am using SQL 2000 Server Enteprise Edition. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 16:36:49
|
| Right click on each of the tables, go to All tasks, then to manage indexes. Post the indexes in here.Tara Kizer |
|
|
|
datzent83
Starting Member
5 Posts |
Posted - 2007-01-09 : 17:37:03
|
| I have 122 tables in my database. Is there an easier way of doing this? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 17:44:17
|
| You only need to check the tables involved in the queries in question that you listed above. I count only 5 tables.Tara Kizer |
|
|
|
datzent83
Starting Member
5 Posts |
Posted - 2007-01-09 : 17:56:48
|
| Tara,I am a little new at this. Can you please give me the steps on how I can copy the indexes to post them here? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-09 : 18:02:50
|
| I don't have time to explain in detail on how to do it right now. Maybe someone will come along and help you do that. But for now if your problem is so urgent, then I'd start typing. I doubt you have many indexes to put in here.Tara Kizer |
|
|
|
|
SQL Query Check and Validate (Urgent)