Solved

Query Runs on Development Edition but do not work on Enterprise and Standard Edition (SQL2000)

Posted on 2011-09-28
9
174 Views
Last Modified: 2012-05-12
Hi,

I have a stored procedure it works great on SQL Server 2000 Developer editon and the query performance is close to 1 min and then we deploy the same stored procedures on
Enterprise and Standard Edition (SQL2000) it does not work, it just keeps on running.

Any idea whats happening when we move the stored procedure to Enterprise and Standard, why it is not working?

Regards,
Rithesh
0
Comment
Question by:Star79
9 Comments
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 36812690
Could you please share the stored procedure details? What it does? The input you provide to the SP in both editions are same?

By debugging the SP you can findout where it goes wrong. http://www.codeproject.com/KB/database/SQLDebuggingVSNET.aspx
http://support.microsoft.com/kb/316549
0
 

Author Comment

by:Star79
ID: 36812723
Everything is exactly the same. The query is very big, i may not be able to send but this is what it does.It creates few temporary table (Clustered Index), queries the records close to 25 million.
It is all select,insert into temp tables which have a clustered index on it.

Can you suggest some pointers?
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 36812840
What error you  are receiving?
0
 
LVL 16

Expert Comment

by:Easwaran Paramasivam
ID: 36812905
I hope the SP you run in the Standard Edition could be cached and it seems like it takes less time. But in Enterprise edition you run first time. As there is no cache this time, it takes long time I guess.

Most probably it would be related to Query fine tuning. Instead of temporary table you could consider using Views/Table Variables. If you use order by then remove and see what happens.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 7

Expert Comment

by:luani
ID: 36813062
Enterprise and Standard Edition (SQL2000) it does not work, it just keeps on running.

Wait until you get an error message or it finishes (if so re-run to see if  speeds up)..also check the enterprise server settings...about memory allocated
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 36814348
so you tested against a 25 million row table in the developer edition?

you have confirmed that all

 indexes are the same in the different environments
 triggers are the same and present
 indexed views/partitioned views

 what about the placement and size of tempdb/ physical server ram...

 are any other processes running on the server  which where not running to the same frequency/spec in the developer environment

are statistics uptodate in the "production" environments

please post the procedure or provide more detail general information...
e.g. how are the temp tables populated?
what loops/cursors are used
how is the stored procedure invoked?
how many users does the environment support?
can/is the procedure executed concurrently by multiple users...

what filters does the procedure apply to the tables it processes...
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 36819127
>>Any idea whats happening when we move the stored procedure to Enterprise and Standard, why it is not working?<<
Because your data, server or some other factor is different.  This has nothing to do with the edition, whatsoever

0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36899849
<<Any idea whats happening when we move the stored procedure to Enterprise and Standard, why it is not working?>>
I concur with previous comments.  What tells you that the deployment failure is due to edition ?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 36899858
From the tenure of your question, I'd say that there is something that prevents your procedure from running in a non-FIFO environment (Production environment are not).  Look at blocking or deadlocking.  Please post the showplan of the procedure and check whether there are any indexes missing.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now