Click here to Skip to main content
Search within:



Next

Sql stored Procedure
Sign Up to vote bad
good
See more: SQL-Server
I have created stored procedure to select record but it may works for sometime and sometimes it gives timeout. Even when there's only 1 record in the database.
Posted 17 Sep '12 - 3:15
bhargavpp1.5K
Edited 17 Sep '12 - 3:22
Malli_S16.9K
v2
Add a Solution

Comments
aspnet_regiis - 17 Sep '12 - 9:22
You need to contact your network administrator. The network connection is dropping...
bhargavpp - 18 Sep '12 - 0:56
CREATE Procedure [dbo].[usp_storedName] ( @Param1 int = NULL, @Param1 int = NULL ) as SET NOCOUNT ON Select * From ( Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition UNION ALL Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition ) as Q1 Where ( @Param1 Is Null or Q1.BranchId = @Param1) And ( @Param2 Is Null or Q1.ParentId = @Param2) this is my stored procedre . when i am executing from database it is fast executing and result in 01 sec while executing from Executing nonwuery then it will take time for sometime and gives timeout the alternate solution of same is replace column name instead of * then i will work for 1 day then after we have to chenge it by column name this alternate will contiune..
Dinesh Ambaliya - 17 Sep '12 - 9:28
There should be something wrong in your stored procedure. We cannot say anything without seeing your stored procedure.
bhargavpp - 18 Sep '12 - 0:56
CREATE Procedure [dbo].[usp_storedName] ( @Param1 int = NULL, @Param1 int = NULL ) as SET NOCOUNT ON Select * From ( Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition UNION ALL Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition ) as Q1 Where ( @Param1 Is Null or Q1.BranchId = @Param1) And ( @Param2 Is Null or Q1.ParentId = @Param2) this is my stored procedre . when i am executing from database it is fast executing and result in 01 sec while executing from Executing nonwuery then it will take time for sometime and gives timeout the alternate solution of same is replace column name instead of * then i will work for 1 day then after we have to chenge it by column name this alternate will contiune..
Marcus Kramer - 17 Sep '12 - 9:29
How about showing us the procedure. Without that how are we suppose to magically guess what that looks like. We don't read minds, even if it sometimes seems like some of the CPians have that ability.
bhargavpp - 18 Sep '12 - 0:56
CREATE Procedure [dbo].[usp_storedName] ( @Param1 int = NULL, @Param1 int = NULL ) as SET NOCOUNT ON Select * From ( Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition UNION ALL Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition ) as Q1 Where ( @Param1 Is Null or Q1.BranchId = @Param1) And ( @Param2 Is Null or Q1.ParentId = @Param2) this is my stored procedre . when i am executing from database it is fast executing and result in 01 sec while executing from Executing nonwuery then it will take time for sometime and gives timeout the alternate solution of same is replace column name instead of * then i will work for 1 day then after we have to chenge it by column name this alternate will contiune..
bhargavpp - 18 Sep '12 - 0:52
not a good one
bhargavpp - 18 Sep '12 - 0:55
CREATE Procedure [dbo].[usp_storedName] ( @Param1 int = NULL, @Param1 int = NULL ) as SET NOCOUNT ON Select * From ( Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition UNION ALL Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition ) as Q1 Where ( @Param1 Is Null or Q1.BranchId = @Param1) And ( @Param2 Is Null or Q1.ParentId = @Param2) this is my stored procedre . when i am executing from database it is fast executing and result in 01 sec while executing from Executing nonwuery then it will take time for sometime and gives timeout the alternate solution of same is replace column name instead of * then i will work for 1 day then after we have to chenge it by column name this alternate will contiune..

1 solution

do one thing just tweak the execution time out for ur stored procedure in .cs page where u call ur stored procedure, also configure ur application
  Permalink  
Posted 17 Sep '12 - 3:30
shabarnadh.vempati1.2K
Comments
Marcus Kramer - 17 Sep '12 - 9:36
Not a good idea because it does not address the root of the issue. The root of the issue is either a connectivity issue as aspnet_regiis notes in his comment, or that the procedure itself has issues within it. Simply increasing a timeout does nothing but mask the issue temporarily.
bhargavpp - 18 Sep '12 - 0:55
CREATE Procedure [dbo].[usp_storedName] ( @Param1 int = NULL, @Param1 int = NULL ) as SET NOCOUNT ON Select * From ( Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition UNION ALL Select t1.column1,t1.column2,t3.column4,t2.column5,t1.column6,t1.column7 From table1 t1 Inner Join table2 t2 On ISD.ItemId = I.ItemId and i.InPcs = 1 and ISD.SubItemId <> 0 Inner Join table3 t3 On I.GroupId = IG.GroupId Where somecondition ) as Q1 Where ( @Param1 Is Null or Q1.BranchId = @Param1) And ( @Param2 Is Null or Q1.ParentId = @Param2) this is my stored procedre . when i am executing from database it is fast executing and result in 01 sec while executing from Executing nonwuery then it will take time for sometime and gives timeout the alternate solution of same is replace column name instead of * then i will work for 1 day then after we have to chenge it by column name this alternate will contiune..
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Sergey Alexandrovich Kryukov 498
1 Arun Vasu 275
2 Maciej Los 273
3 Mahesh Bailwal 264
4 Aarti Meswania 175
0 Sergey Alexandrovich Kryukov 9,660
1 OriginalGriff 7,329
2 CPallini 3,968
3 Rohan Leuva 3,339
4 Maciej Los 2,851


Advertise | Privacy | Mobile
Web01 | 2.6.130516.1 | Last Updated 17 Sep 2012
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid