Any idea on why store procedure is so slow 1

[morechocolate]

I do not if anyone can shed light on this, but we have a stored procedure that runs fine, pershaps about 2 minutes, on our production server. However, when that same stored procedure is run on a different server, one that is to become our production server, the stored procedure seems to just sit there, running for hours. So far the programmer has not even let it finish it has taken so long.

The DBA says he has verfied that the settings are the same in both environments. They (the DBA and the programmer) are now thinking that underlying processes that our vendor has set up for their programming is causing the problem. I really do not know. I just think it is odd that the same exact stored procedure is taking so long to run.

Any thoughts? Sorry I cannot give anymore information than that.

Oh - the stored procedure is using the old ANSI standard for outer joins (don't ask), but I would not think that would cause a problem.

Thanks

 

[SQLDenis]

try updating the statistics


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

Yup, stats first then check the rest - parameter sniffing, hardware specifics, differences in exec plan etc.

Regarding old-style join syntax... are there both left (*=) and right (=*)joins in the same query?



------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[morechocolate]

Thanks for the responses.

No only *=

 

[druer]

I read that the database settings are the same, but are the indexes for the underlying tables also the same?

Are the command options for SQL Server perhaps AWE also the same so that SQL Server can use additional memory you might have?

Are the disks/drive arrays also being used as they are on the current production system or is everything split up on it, and all on 1 disk or something on the new server?

 

[SQLBill]

Are all the tables, etc using the same name?

Does the stored procedure use the full naming convention (\\servername.databasename.ownername.tablename)?

Is the database owner the same?

How is the stored procedure being run? As a job?

-SQLBill

Posting advice: FAQ481-4875

 

[morechocolate]

SQLBill - I am waiting for the programmer to get back to me, but I will answer the questions that I can.

Are all the tables, etc using the same name? Yes

Does the store procedure use the full naming convention? No, but I looked at the stored procedure and noticed, for some reason, there is one instance where he references a table as dbo.table_name, but nowhere else in the code does he do that.

How is the store procedure being run? I think after he tried executing through a program and it was, seemingly hanging, he tried to use the exec command, but with the same results. I will confirm.

Thank you all so much for your feedback.

 

[SQLBill]

Are the tables in both systems OWNED by the same login? (SA, the same user name, etc).

-SQLBill

Posting advice: FAQ481-4875

 

[SQLSister]

Never ever use the old syntax for outer joins. This can be interpreted different ways by SQL server. likely it is doing a cross join. Change it to a correct join syntax and see if this resolves the problem.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[morechocolate]

SQLSister, I have tried for the past year or two to get the programmer to use the latest ANSI standard, obviously, without success. I am not his boss, I am a business analyst who works on the behalf of the business units. Unfortunately, his boss is not a programmer so I am not going to get help there either.

 

[morechocolate]

Here is something I found out about the using of the dbo on one of the objects. The programmer said, and I see this now, he is only using the dbo for the user defined table. He said when he originally coded the stored procedure, the user defined table was not working without specifying it as dbo.user_table.

I am not a DBA, but that says to me something else must have been happening when he created the user defined table because I have created them and I have not run into the same issues.

 

[morechocolate]

Well all this is what I did. I took the select statement of the code changed it to use the latest ANSI standard for the outer joins and it ran in 52 seconds. I let the programmer know this and he is going to incorporate into the stored procedure. We'll see how things go. I will keep you posted.

 

[SQLSister]

morechocolate, to help you out in convincing the programmer (or at least his boss), a quote from Books online

In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax.

From what I've seen, this is often interpreted as a cross join which joins every record of the first table to every record onthe second table. This is usually a bad thing. TWo table with 100 records each will result in a million records after the join. You can imagine what happens if you have a million records in the tables! This is a business critacl problem that the man's boss must solve as it is a very bad practice and often results in poor performance and/or inaccurate results. To make matters worse the programmer often then uses the distinct keyword to get the actual data needed when the cross join happens, so he creates a million records and then filters back down to the 1000 he wants.

Good luck in convincing this guy. But truly this syntax will not work properly even if it appears to be doing so. You cannot rely onthe results from this syntax.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[vongrunt]

More ammo:

1) Let's say there are 15 conditions in WHERE clause. Which ones do filtering (WHERE) and which ones perform logical linking between tables (JOIN)? Repeat that test 6 months after SQL code went into production stage .

2) Developer is in a hurry, writes WHERE clause with left foot and forgets to wrap conditions linked with OR clause into (). Wrong results aside, server will generate ugly execution plan - probably with lazy spools and all that stuff.

3) ScriptKiddie hax0r is teaching his SQL injection 101 lesson. So he does L1 or L2 attack. Depending on location of injection point server will generate more or fewer cross joins. Sorta like denial of service and vanilla hack all in one package. With ANSI syntax at least cross joins cannot happen. The rest is of course security thing.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[morechocolate]

Thanks everyone.

SQLSister, I will certainly give it a try. vongrunt - too funny. Actually, vongrunt, I think in addition to the standard that was being used, your point 1 was also occurring.