Em Andamento

Check functionality of conversion of dynamic sql code to ordinary sql

Hello,

I have a SQL Server 2008 stored procedure ([url removed, login to view]) that intermittently runs very slowly. Normally it completes in under 2 seconds, but occasionally it takes 75 seconds to complete. It runs dynamic SQL (i.e. it builds a string inside the procedure and runs it as a sql command).

I think that the reason it sometimes runs slow is that SQL Server builds a new query plan each time it runs, and sometimes this plan is bad, causing the slow run.

Although I could try to get SQL Server to store a saved plan by replacing:

exec sql

with:

sp_executesql

I have decided instead to rewrite it as a normal query (i.e. without using dynamic SQL).

The purpose of this project is to ask you to compare the old one with the new one I've written, and check that the functionality of the new one is exactly the same as the old one, whatever the parameters.

Really just to check that I haven't made any silly mistakes.

The old stored procedure is in:

[url removed, login to view]

The new stored procedure is in:

[url removed, login to view]

and the new one uses a function "udf_CommaStringToTable" in:

[url removed, login to view]

Any other thought or suggestions would be welcomed.

An example of how the stored procedure is called, with parameters, is in the comments section of the new stored procedure.

These parameters can vary.

Note I have substituted for the at character in this listing, because rentacoder doesn't like that character.

Many Thanks,

Neil Miller

Windmill IT Ltd

## Deliverables

1) All deliverables will be considered "work made for hire" under U.S. Copyright law. Employer will receive exclusive and complete copyrights to all work purchased. (No 3rd party components unless all copyright ramifications are explained AND AGREED TO by the employer on the site per the worker's Worker Legal Agreement).

## Platform

SQL Server 2008 (not RC2) running on Windows 2008 DataCentre edition.

select version gives:

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) Mar 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.0 (Build 6002: Service Pack 2)

Habilidades: Administração de Bancos de Dados, MySQL, SQL

Ver mais: written service agreement, service dynamic, how to string, how old is to, hire check, example of service agreement, example of a service agreement, example of a note, dynamic hire, c. t. corporation, build mysql query, t-sql with, t-sql in, how to hire a worker, X64, windmill, t-sql;, sql mysql, sql microsoft, Sql code, sql and c, sp1, ordinary, neil, miller

Acerca do Empregador:
( 4 comentários ) Tadley, United Kingdom

ID do Projeto: #2965888

Premiar a:

vw7868770vw

See private message.

$80.75 USD em 3 dias
(2 Avaliações)
3.0

7 freelancers are bidding on average $106 for this job

oleg21

See private message.

$63.75 USD in 3 dias
(134 Comentários)
7.0
michaeldstephens

See private message.

$25.5 USD in 3 dias
(90 Comentários)
5.7
vw7677496vw

See private message.

$42.5 USD in 3 dias
(4 Comentários)
2.2
mastersoft1

See private message.

$250 USD in 3 dias
(3 Comentários)
2.1
skghoshvw

See private message.

$21.25 USD in 3 dias
(0 Comentários)
0.0
guardianpressinc

See private message.

$255 USD in 3 dias
(2 Comentários)
0.0