Extremly bad performance Stored Procedures

The last few days my sqlserver executes stored procedures badly.
When I run the Query Analyzer to execute a certain stored procedure it takes more than 2 minutes to execute the procedure. When I copy the contents of de SP to Query analyser to run it as an sql statement it find's the results within a second.
This behavior dissapear's after a while and comes back randomly.
I had the problem Friday afternoon then tuesday and now again.
Between these day's my sqlserver works fine.
Can anybody please help me with this problem.Have you tried using the "with recompile" option ? Your query plan is probably based on an outdated data distribution or schema. Running the "with recompile" option will regenerate the query plan. Also, are you parameters to the stored procedure vary enough that the execution plans change ? Do a comparison in query analyzer - using show execution plan.|||I was able to elimante the problem by altering de SP.
In the SP there where more than 4 joins to the same table.
When I made a user defined function and replaced those joins with this function, it all works fine.

But one question remains. How is it possible that the query analyser didn't have problems with the joins but de SP did have?|||Did you try the recompile ? Sometimes, if your table(s) involved in the query change enough - the query plan needs to change as well. When you run it in query analyzer, the query plan is generated dynamically. For the sp, it could still be using the original query plan when you created it. That is why I suggested to run the 2 in query analyzer with the "show execution plan".

Extracting Data from Badly Designed Table

I've inherited a crappy data structure on a project, see table below
...
CREATE TABLE [REPL_COMPONENT]
(
[ReplacementID] [int] IDENTITY (1, 1) NOT NULL ,
[RepairID] [int] NULL ,
[Repl1] [int] NULL ,
[ReplCost1] [int] NULL ,
[ReplType1] [int] NULL ,
[Repl2] [int] NULL ,
[ReplCost2] [int] NULL ,
[ReplType2] [int] NULL ,
[Repl3] [int] NULL ,
[ReplCost3] [int] NULL ,
[ReplType3] [int] NULL ,
[Repl4] [int] NULL ,
[ReplCost4] [int] NULL ,
[ReplType4] [int] NULL
)
I'd like to write a query to extract this data in normal form. So, for
each row in this table, my query would return 4 rows.
Given, this one row of dummy data:
INSERT INTO [REPL_COMPONENT]([RepairID], [Repl1], [ReplCost1],
[ReplType1], [Repl2], [ReplCost2], [ReplType2], [Repl3], [ReplCost3],
[ReplType3], [Repl4], [ReplCost4], [ReplType4])
VALUES(53, 11, 11, 11, 22, 22, 22, 33, 33, 33, 44, 44, 44)
The new query would return 4 rows
1 53 11 11 11
1 53 22 22 22
1 53 33 33 33
1 53 44 44 44
Appreciate your help!Simple:
select ReplacementID, RepairID, Repl1, ReplCost1, ReplType1 from
REPL_COMPONENT
union all
select ReplacementID, RepairID, Repl2, ReplCost2, ReplType2 from
REPL_COMPONENT
union all
select ReplacementID, RepairID, Repl3, ReplCost3, ReplType3 from
REPL_COMPONENT
union all
select ReplacementID, RepairID, Repl4, ReplCost4, ReplType4 from
REPL_COMPONENT
*mike hodgson*
http://sqlnerd.blogspot.com
George wrote:

>I've inherited a crappy data structure on a project, see table below
>...
>CREATE TABLE [REPL_COMPONENT]
> (
>[ReplacementID] [int] IDENTITY (1, 1) NOT NULL ,
>[RepairID] [int] NULL ,
>[Repl1] [int] NULL ,
>[ReplCost1] [int] NULL ,
>[ReplType1] [int] NULL ,
>[Repl2] [int] NULL ,
>[ReplCost2] [int] NULL ,
>[ReplType2] [int] NULL ,
>[Repl3] [int] NULL ,
>[ReplCost3] [int] NULL ,
>[ReplType3] [int] NULL ,
>[Repl4] [int] NULL ,
>[ReplCost4] [int] NULL ,
>[ReplType4] [int] NULL
> )
>I'd like to write a query to extract this data in normal form. So, for
>each row in this table, my query would return 4 rows.
>Given, this one row of dummy data:
>INSERT INTO [REPL_COMPONENT]([RepairID], [Repl1], [ReplCost1],
>[ReplType1], [Repl2], [ReplCost2], [ReplType2], [Repl3], [ReplCost3],
>[ReplType3], [Repl4], [ReplCost4], [ReplType4])
>VALUES(53, 11, 11, 11, 22, 22, 22, 33, 33, 33, 44, 44, 44)
>The new query would return 4 rows
>1 53 11 11 11
>1 53 22 22 22
>1 53 33 33 33
>1 53 44 44 44
>Appreciate your help!
>
>|||George,
rather than try to work round a 'crappy' data structure, why not simply
re-invent it and normalize the table at an early stage. Log-tern benifits
usually outweigh the short-term effort.
Tony
"George" wrote:

> I've inherited a crappy data structure on a project, see table below
> ...
> CREATE TABLE [REPL_COMPONENT]
> (
> [ReplacementID] [int] IDENTITY (1, 1) NOT NULL ,
> [RepairID] [int] NULL ,
> [Repl1] [int] NULL ,
> [ReplCost1] [int] NULL ,
> [ReplType1] [int] NULL ,
> [Repl2] [int] NULL ,
> [ReplCost2] [int] NULL ,
> [ReplType2] [int] NULL ,
> [Repl3] [int] NULL ,
> [ReplCost3] [int] NULL ,
> [ReplType3] [int] NULL ,
> [Repl4] [int] NULL ,
> [ReplCost4] [int] NULL ,
> [ReplType4] [int] NULL
> )
> I'd like to write a query to extract this data in normal form. So, for
> each row in this table, my query would return 4 rows.
> Given, this one row of dummy data:
> INSERT INTO [REPL_COMPONENT]([RepairID], [Repl1], [ReplCost1],
> [ReplType1], [Repl2], [ReplCost2], [ReplType2], [Repl3], [ReplCost3],
> [ReplType3], [Repl4], [ReplCost4], [ReplType4])
> VALUES(53, 11, 11, 11, 22, 22, 22, 33, 33, 33, 44, 44, 44)
> The new query would return 4 rows
> 1 53 11 11 11
> 1 53 22 22 22
> 1 53 33 33 33
> 1 53 44 44 44
> Appreciate your help!
>