SQL Server Performance Forum – Threads Archive

Query Problem in execution

Hi,
I am using sql server 2000 . after few days in our application gets slow to display the details. I have used sql profiler to check which is the query takes more time for execution… This is the one trace in the sql profiler. it takes minutes to execute.
i do not understand what is this and why it is executing when the query gets executed? Sql profiler output:
declare @P1 int
set @P1=234
exec sp_prepexec @P1 output, NULL, N’select distinct PROJECT.PROJECT_KEY from PROJECT PROJECT,WORKFLOW_PROFILE_USER WFPU,WORKFLOW_PROFILE WP, WORKFLOW W Where PROJECT.project_key in (21534,21535) AND W.WORKFLOW_KEY= WP.WORKFLOW_KEY AND PROJECT.PROJECT_SHORT_NAME like”%%” OR PROJECT.PROJECT_LONG_NAME like”%%”
select @P1 Thanks Devteam

---

Hi,
First of all you need to redesign your query.
You are accessing only project_key from project table then there is no need for selecting from other tables.
Also after this the value can only be either 21534 or 21535 because of IN
so try to simulate only that in your query.

---

You created cartezian product (cross join) because you have join condition just between wrokflow and workflow_profile table. Use ansi syntax to avoid that type of mistakes.

---

Hi,
How to check the problem exist in tomcat or sql server… after few days running our application, each page takes time to display the details. at that time when i restarted the tomcat then the page details gets load faster. So problem exist in tomcat or in sql server 2000?
i have checked the queries takes more time to execute using sql profiler. Thanks

---

Have you read responses at all?

---

HI,
Run the profiler copy the Script which take time for execution,Run execution plan in Query Analyser find the field to be index & do indexing on the tables .This will increase the speed.

---

]]>