I have a big problem with slow execution of stored procedure in SQL Server 2005 but I really don't understand the reason. I have a database with large table (about 400 million rows) and simple stored procedure to get data from that table (one select statement to select time and value columns).
Strange thing is that if I call that stored procedure from .net application (native SqlDataProvider) it takes about 6 seconds to execute but if I call the same procedure with the same parameters from within SQL Server Management Studio it takes only 25 milliseconds to execute!
I've noticed that from .net, procedure is called with binary data and in Management Studio sql script is executed so I've copied/pasted the script from Management Studio to .net code and again the same thing happens (6 seconds from .net and 25ms from Management Studio). I traced executions with SQL Profiler and everything seems to be identical for both applications except it takes much longer time for .net application.
Both SQL Server Management Studio and .net application are on the same machine and SQL Server is on another.
This is the query that when executed in Management Studio takes 25ms:
At first I thought that Management Studio somehow caches results but if I change parameters of stored procedure it always takes less than 30ms to execute.
I really don't understand this. Please, help!
Dear friends,I have a problem with a simple select statement and I don't know why it is happening.I have 2 tables, Fees and FeesDataRoles. Fees presents all the fees and FeesDataRole is a middle table between Fees and Roles table. So each fee can have multiple Roles and a Role can have many Fees.Now I have a select statement:Select *From Fees Inner Join FeesDataRoles ON Fees.FeeID = FeesDataRoles.FeeIDWhere (FeesDataRoles.DataRoleID = @DataRoleID) AND (FeesDataRoles.RecordStatus = 1 ) AND (FeesDataRoles.ValidFrom >= getdate() ) AND ( FeesDataRoles.ValidTo <= getdate() OR FeesDataRoles.ValidTo is null)Now it shouldn't take that long to execute this procedure but surprisingly sometimes when I insert a value to the table and then execute this store procedure it does now show the data just added. Very strange.....!!!!I ran the procedure 5 times after inserting an item and nearly 1 out of 5 does not return the right result righ. ( It does not include the recently inserted rows)Anyone have any idea....?I used Tuning Advisor, no sugestion. I change the clustered index in FeesDataRoles from FeesDataRoleID(the primary key of the table) to DataRoleID to increase the performance, still it happens sometimes.Is my Where clause so costly that cause this problem.Please help. I really appreciate your help.Regards,Mehdi
Has anyone encountered cases in which a proc executed by DTS has the following behavior: 1) underperforms the same proc when executed in DTS as opposed to SQL Server Managemet Studio 2) underperforms an ad-hoc version of the same query (UPDATE) executed in SQL Server Managemet Studio
What could explain this?
Obviously,
All three scenarios are executed against the same database and hit the exact same tables and indices.
Query plans show that one step, a Clustered Index Seek, consumes most of the resources (57%) and for that the estimated rows = 1 and actual rows is 10 of 1000's time higher. (~ 23000).
The DTS execution effectively never finishes even after many hours (10+) The Stored procedure execution will finish in 6 minutes (executed after the update ad-hoc query) The Update ad-hoc query will finish in 2 minutes
When i execute a stored procedure it generally takes about half a second to run but sometimes it takes 20 to 30 seconds. I am the only one using the server so I know it is not due to other traffic. I have looked at Profiler and nothing looks out of the ordinary. Another observation is that the slow ones are always near eachother. I will have about 10 fast executions and then 3 slow ones and then back to fast ones. Has anyone seen anything like this before?
Is there anyway to write the following stored procedure without the loop so that it goes much faster? :confused:
---------------------------------------------------------------------------- use MJ_ReportBase go if exists(select 1 from sysobjects where type='P' and name='sp_Periode') begin drop procedure sp_Periode end go create procedure sp_Periode @start int , @stop int as declare @x int
set @x = 0 set @x=@start
delete from tbl_periode
while (@x>=@stop) begin
-- --- -- --- -- Create table tbl_inout if exists(select 1 from sysobjects where type='U' and name='tbl_inout') begin drop table tbl_inout end
select datetimestamp,accname,badgeid,personname,inoutreg into tbl_inout from WinXS..x18 where convert(varchar,datetimestamp,120)+' '+ltrim(str(id))+' '+ltrim(str(badgeid)) in (select convert(varchar,max(datetimestamp),120)+' '+ltrim(str(max(id)))+' '+ltrim(str(badgeid)) as datetimestamp from WinXS..x18 where (accname='Kelder -1' or accname='Tnk Entree') and convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@x),105) group by badgeid) and badgeid>0 order by personname
-- --- -- --- -- Create table tbl_result
if exists(select 1 from sysobjects where type='U' and name='tbl_result') begin drop table tbl_result end
-- --- -- ---
select convert(varchar,datetimestamp,105) 'DATUM' , badgeid 'PAS' , initials 'VOORNAAM' , personname 'NAAM' , convert(varchar,min(datetimestamp),108) 'MIN' , convert(varchar,max(datetimestamp),108) 'MAX' into tbl_result from WinXS..x18 where convert(varchar,datetimestamp,105)=convert(varchar ,getdate()-abs(@x),105) and accname in ('Kelder -1','Tnk Entree') and badgeid>0 group by convert(varchar,WinXS..x18.datetimestamp,105) , badgeid , initials , personname order by initials , personname asc , convert(varchar,datetimestamp,105) asc
-- --- -- --- -- Rapportage tabel
insert into tbl_periode select tbl_result.datum as DATUM , ltrim(ltrim(rtrim(tbl_result.naam))+' '+ltrim(rtrim(isnull(tbl_result.voornaam,' ')))) as NAAM , tbl_result.min as MIN , tbl_result.max as MAX , case tbl_inout.inoutreg when 1 then 'in' when 2 then 'out' else 'err' end as [IN/OUT] , substring('00000',1,5-len(tbl_result.pas))+ltrim(str(tbl_result.pas)) as PAS from tbl_inout,tbl_result where tbl_result.datum+' '+tbl_result.max+' '+ltrim(str(tbl_result.pas)) = convert(varchar,tbl_inout.datetimestamp,105)+' '+convert(varchar,tbl_inout.datetimestamp,108)+' '+ltrim(str(badgeid)) order by tbl_result.naam asc
We have a stored procedure which is running fine on a SQL server 2000 from Query Analyzer. However, when we try to execute the same stored procedure from ADO.NET in an executable, the execution is hung or takes extremely long. Does anyone have any ideas or suggestions about how it could happen and how to fix. thanks
Hi all, I have a webpage with a Datagrid that populates using a table adapter from a Stored procedure that exists in my SQL Database...If I run the Stored procedure in SQL Directly then it takes 20 Secs to return all records...If I run the webpage then it takes just over 20 Secs.. Great you say..But If I have the sorting option set in ASP.net and I click on a column to sort then off the page goes for another 20 secs to sort the data.. Is there a better way to do what I am doing here that will speed up the page load.. Ie..the data is returned once and then sorted... Is it Better / Quicker for me to create a table using the stored procedure and link to this from the website..Updating the table every couple of minutes ? Any advice please ? Ray..
I have developed a stored procedure that filters a view that is a union of several different tables. This provides status information for items across our warehouse management system. This system seems to work very well and normally processes results very quickly (< 3 seconds). However, occasionally (every few days) we begin to see timeouts on the query after 3 minutes of processing. I can watch this process in SQL Profiler and see that the query is timing out after 180 seconds, which is the timeout we have for the query within the DAL. When I copy the line from the SQL Profiler and execute it directly in SSMS, the query executes in less than 2 seconds. I first thought that somehow this had to do with execution plans, but when I try to reload the page again, which executes the query, it still times out. I did add a OPTION(KEEPFIXED PLAN) to the sproc, and that seemed to speed things up for the time, but I am not sure if this is even the problem and what the optimal solution would be. Any thoughts spring to mind? Thanks, Steve
I have a big table A_newHistory (more than 2 million rows) with primary key fund_id + date_price . This table has to be updated every 2 hours from XML. Every row in XML must be inserted or updated (if current id and date already exist in the table) in the A_newHistory. The following procedure works but very slow... How can I optimize that?
================================================== ======= CREATE PROCEDURE spSaveFundsAdjustedClose @XML ntext AS DECLARE @fund_id int DECLARE @date_price datetime DECLARE @adj_closed float DECLARE @XMLDoc int
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRANSACTION
EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @XML
DECLARE MutualFunds_Cursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT * FROM OPENXML (@XMLDoc , '/xml/a', 1) WITH ([id] INT,[date] datetime, price float) OPEN MutualFunds_Cursor FETCH NEXT FROM MutualFunds_Cursor INTO @fund_id, @date_price, @adj_closed
WHILE @@FETCH_STATUS = 0 BEGIN IF EXISTS (SELECT * FROM A_newHistory WHERE id_fund = @fund_id AND date_price = @date_price) BEGIN UPDATE A_newHistory SET adj_close = @adj_closed WHERE id_fund = @fund_id AND date_price = @date_price END ELSE BEGIN INSERT INTO A_newHistory VALUES(@fund_id, @date_price, @adj_closed) END
IF @@Error <> 0 BEGIN ROLLBACK TRANSACTION SELECT -1 RETURN END
FETCH NEXT FROM MutualFunds_Cursor INTO @fund_id, @date_price, @adj_closed END
EXEC sp_xml_removedocument @XMLDoc CLOSE MutualFunds_Cursor DEALLOCATE MutualFunds_Cursor
COMMIT TRANSACTION SELECT 0 GO ================================================== =======
Hi,Plz, I need some info (SQL2000) :)A stored procedure is like this:"Select table1.id, table1.txt, (select table2.nr from table2 wheretable2.fk_table1=table1.id) as nr where table1.id<>10"The essence here is that "select table2.nr from table2 wheretable2.fk_table1=table1.id" returns either the integer in table2.nr, or NULLif there isnt a match. The whole sentence runs EXTREMELY slow...3-4 sec.What is wrong?"select table2.nr from table2 where table2.fk_table1=table1.id" runs quicklyoutside the stored procedure. The original sentence without the "nr" (Selecttable1.id, table1.txt where table1.id<>10) runs quickly too...But together it slows down dramatically..why? I should mention that thesub-query could return NULL if theres no match in table2...But i cant seewhy that should slow things down (remember - it runs fine outside the SP)?Thx,PipHans---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.518 / Virus Database: 316 - Release Date: 11-09-2003
1) Is it possible to run stored procedures at specified intervals without using the job system (through T-SQL)? I want the schedule to be independent of the MSDB database in case of temporary failures, etc.
2) Would extended stored procedures be helpful in this scenario?
i have a stored procedure that builds a dynamic insert statement & inserts data into a table. Now when I execute the sp manually with a 'exec sptest parm1,parm2', it runs fine & inserts the data in the table. But when this sp is called from within a .net application,it prepares the insert statement but does not actually insert the record in the table. It comes back with a RPC: Completed so it seems like it completed but it does not insert the record in the table. Also just after the RPC:Completed, it throws an ATTENTION with nothing in the text data. I am confused on whats going on here. The definition of ATtention in the event class implies that the query has been cancelled or it timed out. But we have no timeout on the sql server side. The application developer says there is no timeout on the application side (i dont totally believe that). so what else could cause that Attention? There is nothing in the error log as well. Also why does the trace come back with a RPC:Completed when the stored procedure did NOT insert any data? Does the RPC:Complete only mean that the RPC completed - irrespective of success or failure? If the sp failed or had an error will it still come back with a rpc:completed? Any thoughts are appreciated...
i want to know the execution status of a stored procedure . That is i want to know whether the stored procedure was executed succesfully or not.If not i want to get the error message
I am looking for a way to count the number of times a stored procedure on the database has been executed over let's say over a period of time(month, years, etc).
Is there a system stored procedure or a system table that stores that information.
I am struggling to find some information about this topic Thanks for the help
Hi. When SqlServer executes a procedure (any type: select, update, insert) after it´s executed can I get a default status for this executed procedure, like a return bool value from SqlServer as true for successfull and false for failed to execute?
Hi,There is a stored procedure which runs through job.It is calling to other stored procedure and other stored procedures arecalling to another .. so on (approx 12-15 sp in batch)Problem:Sometime it does not execute properly. (approx very rare... once in 500execution or sometime on new site/database)I want to know the reason for it.If anybody have faced the similar problem.Please tell the possible causes and possible solutions.Thanks in Adv.T.S.Negi(MIND)
SUM(dbo.[NMDS Data for IDF Report].[Number of caseweighted discharges]) AS Expr1, dbo.AdmissionMappingTable.Admission
FROM dbo.DomicileCodes INNER JOIN
dbo.[NMDS Data for IDF Report] ON dbo.DomicileCodes.[Domicile code] = dbo.[NMDS Data for IDF Report].[Domicile Code] INNER JOIN
dbo.PurchaseUnitMappingTable ON dbo.[NMDS Data for IDF Report].[Purchase Unit] = dbo.PurchaseUnitMappingTable.PU INNER JOIN
dbo.AdmissionMappingTable ON
dbo.[NMDS Data for IDF Report].[Admission Type Description] = dbo.AdmissionMappingTable.[Admission Type Description] INNER JOIN
dbo.Agency ON dbo.[NMDS Data for IDF Report].[Agency Name] = dbo.Agency.Agengy INNER JOIN
dbo.DHBMappingTable ON dbo.DomicileCodes.[DHB area] = dbo.DHBMappingTable.[DHB Code]
WHERE (dbo.[NMDS Data for IDF Report].[Financial Year] = '20062007')
GROUP BY dbo.DHBMappingTable.[DHB Name], dbo.Agency.DHB_service, dbo.PurchaseUnitMappingTable.PU, dbo.AdmissionMappingTable.Admission
HAVING (dbo.Agency.DHB_service = @dhb_service) and
AND (dbo.DHBMappingTable.[DHB Name] = @dhbname )
The values of " @dhb_service" and "@dhbname" need to be entered when the stored procedure is executed. Now when I execute the stored procedure through the following statement:
I created the an assembly and stored procedure using the following steps:
sp_configure 'clr enabled', 1 GO RECONFIGURE GO
alter database TEST set trustworthy on
CREATE ASSEMBLY ClrWebServices FROM 'D:Dataclr_4.dll' WITH PERMISSION_SET = UNSAFE; GO
CREATE ASSEMBLY [ClrWebServices.XmlSerializers] FROM 'D:Dataclr_4.XmlSerializers.dll' WITH PERMISSION_SET = SAFE; GO
CREATE PROCEDURE InsertLocation(@city nvarchar(200), @state nvarchar(200), @country nvarchar(200)) AS EXTERNAL NAME ClrWebServices.StoredProcedures.clr_4 GO
After this when i am trying to execute the procedure InsertLocation
EXEC InsertLocation 'Sarasota','Florida','USA'
I am getting the following exception..
Msg 6522, Level 16, State 1, Procedure InsertLocation, Line 0 A .NET Framework error occurred during execution of user-defined routine or aggregate "InsertLocation": System.Net.WebException: Unable to connect to the remote server ---> System.Net.Sockets.SocketException: No connection could be made because the target machine actively refused it System.Net.Sockets.SocketException: at System.Net.Sockets.Socket.DoConnect(EndPoint endPointSnapshot, SocketAddress socketAddress) at System.Net.Sockets.Socket.InternalConnect(EndPoint remoteEP) at System.Net.ServicePoint.ConnectSocketInternal(Boolean connectFailure, Socket s4, Socket s6, Socket& socket, IPAddress& address, ConnectSocketState state, IAsyncResult asyncResult, Int32 timeout, Exception& exception) System.Net.WebException: at System.Net.HttpWebRequest.GetRequestStream() at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at ClrWebServices.Test.TerraService.GetPlaceFacts(Place place) at StoredProcedures.GetLocationImage(SqlString city, SqlString state, SqlString country) at StoredProcedures.clr_4(SqlString city, SqlString state, SqlString country)
I searched in the google and found one answer in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=76222&SiteID=1 suggesting use
ALTER ASSEMBLY ClrWebServices WITH PERMISSION_SET=EXTERNAL_ACCESS
when i executed i got the exception
Msg 6213, Level 16, State 1, Line 1 ALTER ASSEMBLY failed because method "add_ConvertLonLatPtToNearestPlaceCompleted" on type "ClrWebServices.Test.TerraService" in external_access assembly "clr_4" has a synchronized attribute. Explicit synchronization is not allowed in external_access assemblies.
I want to simulate 100 users simultaneously executing a procedure in sql server (best example would be 100 users using one report with different parameters in ssrs at the same time ). can i do that ?
Hi all,I have a problem with a stored procedure.This stored procedure inserts around bout 500,000 records but when it is executed it takes about 15-16 hours to do so.The stored procedure is using a temporary table to do this and is also calling a function.Please let me know if there is a way to reduce the execution time.will a cursor help? Thanks, Anne.
Hello friends! I have one query regarding execution of SSIS package through Stored Procedure.
I have created SSIS package which extract data from database and put that into various text files.Here I am using two global variables one is for Department ID and another is path where I wanna to place my text files as per departments.When I ran it through command prompt it works fine but now I want that dtsx package to run from stored procedure with same input parameters
Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8} Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. End Error Error: 2007-02-22 11:31:37.32 Code: 0xC0011002 Source: {8A27E8DF-051B-4F6B-9538-85BED1F161D8} Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loadin g a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" because of error 0xC0011002. Description: Failed to open package file "C:setupSSIS PackagesSSIS Package File Extract DataSSIS Package File Extract DatainDataExtract.dtsx" due to error 0x80070003 "The system cannot find the path specified.". This happens when loading a package and the file cannot be opened or loaded correctly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
And also I am not understand where i should pass my two input parameters which I used in SSIS package variables???????
i have a few stored procedures called by an application that i would like to create execution plans for every time they are run. is there a way to do this? or are execution plans only done through the Sql Server Query Editor? these queries make comparisons between a temp table and a master table and setting up the data for the tables is time consuming. so id like to automate it by setting the store procedure to create the execution plan at runtime.
HI, I have an interesting situation. I have created a stored procedure which has a select union query and it accepts some parameters. When I execute this procedure it takes 8 minutes. When I copy the script in stored procedure and run it directly in Query Analyzer it takes 2 1/2 minutes?? Same numbers of rows are returned either way in the result set with about 13,000.
I cannot figure this out and it is almost the same thing except that in Query Analyzer I declare the parameters variables and its values?
I think not. Microsoft says it is possible: one for parallel and one for serial execution. Don't believe that's possible for a stored procedure to change execution plans on the fly. Have an on-going problem with timeout occurring with an application and narrowed the culprit to a stored procedure. I couldn't find any obvious issues database wise, no locks, etc. so I recompiled (altered) the sproc without making any changes and the issue cleared for a couple days.
It happened again to day, and so I recompiled (altered) the sproc and it went away again. No code changes to both application (so they say) and stored procedure. I ran the below code snippet to check for sprocs with multiple cached plans and the offending one came up on a short list. So, my question is, Is it one sproc per query plan or can there be more than one. I understand the connection issues.
I have a Stored Procedure (SP) that creates the data required for areport that I show on a web page. The SP does all the work and justreturns back a results set that I dump in an ASP.NET DataGrid. The SPtakes a product area and a start and end date as parameters.Here are the basics of the SP.1.Create temp table to store report results, all columns are createdthat will be needed at this point.2.Select products and general product data into the temp table.3.Create a cursor that loops through all the products in the temptable, running a more complex query with each individual product.4.The results of that query are updated on the temp table based on thecurrent product of the cursor.5.A complex "totals" query is run and the results from that areinserted into the temp table as the last 3 rows.In all we are talking about 120 rows in the temp table with 8 columnsthat are mostly numbers.I originally wrote this report SP about a month ago and it worked fine,ran in about 10 - 20 seconds based on server traffic and amount ofdata in the temp table. For the example I'm running there are the120 products.Just yesterday the (SP started timing out and when I ran the SPmanually from Query Analyzer (QA) (exec SP_NAME ... ) with the sameparameters as it was getting in the code it took 6 minutes to complete.I was floored. I immediately copied the SQL out of the SP and pastedinto another QA window, changed the variables to be hard coded valuesand ran it. It completed in 10 seconds.I'm really confused now. I ran a Profiler on the 2 when I ran themagain. The SQL code in QA executed again in ~10 seconds with 65,000reads. When the SP finished some 6 minutes later it had completed witthe right results but it needed 150,000,000 reads to do its job.How can the exact same SQL code produce such different results (time,disk reads) based on whether its in a SP or just run from QA but stillgive me the exact same output. The reports both look correct and havethe same numbers of rows.I asked my Sys Admin if he had done anything to anything and he saidno.I've been reading about recompiles and temp table indexes and allkinds of other stuff that could possibly be affecting it but havegotten nowhere.Any ideas are appreciated.
I'm trying to get the execution plan for a single stored procedurefrom Profiler. Now, I've isolated the procedure but I get allexecution plans. Any ideas on how to connect the SPIDs so that I onlyget the execution plan for the procedure I'm watching and not thewhole of the server?
I hav the following problem. I have written an stored procedure in sql server 2000 as the following CREATE PROCEDURE dbo.pa_rellena @pFechaInicio datetime
AS declare @pFechaFin datetime declare @auxcod_cen char(10)
--Borramos las tablas temporales si las hemos creado con anterioridad y no se han borrado if object_id('tmpCentros') is not null drop table tmpCentros
if object_id('tmpCentros2') is not null drop table tmpCentros2
if object_id('tmpMaxCajas') is not null drop table tmpMaxCajas
if object_id('tmpCajasCentro') is not null drop table tmpCajasCentro
if object_id('tmpVales') is not null drop table tmpVales
if object_id('tmpDiarioEfectivo') is not null drop table tmpDiarioEfectivo
if object_id('tmpDiarioTalones') is not null drop table tmpDiarioTalones
if object_id('tmpDiarioTarjetas') is not null drop table tmpDiarioTarjetas
if object_id('tmpDiarioSegundaForma') is not null drop table tmpDiarioSegundaForma
if object_id('tmpDiarioGastosTarjetas') is not null drop table tmpDiarioGastosTarjetas
if object_id('temp1') is not null drop table temp1
--Seleccionamos todos los centros de Salvador Bachiller select * into tmpCentros2 from centros where centros.tienda=1 order by cod_cen
--Seleccionamos el maximo de cajas por cada centro
select cod_cen, max(cod_caja) as cajas into tmpMaxCajas from cierrecaja where fecha>=@pFechaInicio and fecha<@pFechaFin group by cod_cen order by cod_cen
--Mezclamos los centros con el maximo de cajas select c.cod_cen, c.Centro, c.Direccion, c.localidad, c.provincia, c.cpostal, c.telefono, m.cajas, operaciones, cajas_tot, tienda, franquicia into tmpCentros from tmpCentros2 as c left outer join tmpMaxCajas as m on c.cod_cen=m.cod_cen
--Cajas por centro select distinct cod_cen as cod_cen, cod_caja as cod_caja into tmpCajasCentro from cierrecaja where fecha>=@pFechaInicio and fecha<@pFechaFin
--Los vales de cada centro select cod_cen,sum(importe) as imp1 into tmpVales from vales where fecha>=@pFechaInicio and fecha<@pFechaFin group by cod_cen
--Efectivo de cada centro select cod_cen,'01' as vendedor,'EFECTIVO' as descripcion, (sum(diario.TotEuro)-Sum(Diario.Imppa2)) as importe1,0 as exp1, (sum(Diario.TotEuro)-sum(Diario.imppa2)) as importe2 into tmpDiarioEfectivo from diario where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='01' group by cod_cen
--Talones por centro select centros.cod_cen,'02' as vendedor,'TALONES' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro) as importe2 into tmpDiarioTalones from centros inner join diario on centros.cod_cen=diario.cod_cen where fecha>=@pFechaInicio and fecha<@pFechaFin and diario.cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='02' group by centros.cod_cen
--Tarjetas por centro select cod_cen,'03' as vendedor,'TARJETAS' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro*(FPago.Descuento/100)) as importe2, sum(Diario.TotEuro) - sum(Diario.TotEuro*(FPago.Descuento/100)) as importe3 into tmpDiarioTarjetas from FPago left join Diario on fpago.Cod_pago=Diario.cod_pago where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen
--Segunda Froma de Pago select cod_cen,'03' as vendedor,'TARJETAS' as descripcion,sum(diario.imppa2) as importe1 into tmpDiarioSegundaForma from fpago left join Diario on Fpago.cod_pago=diario.cod_pa1 where fPago.cod_pago<>'99' and fecha>=@pfechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen
--Comisiones tarjetas de pago select cod_cen,'10' as vendedor, 'GASTOS (-)' as descripcion, sum(Diario.imppa2*(fPago.Descuento/100)) as importe2 into tmpDiarioGastosTarjetas from Fpago left join Diario on FPago.cod_pago= Diario.cod_pa1 where fPago.cod_pago<>'99' and fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0 group by cod_cen /* --Venta neta por centro declare cursortemporal cursor for select cod_cen from TmpCentros2
open cursortemporal delete detallecaja_aux fetch next from cursortemporal into @auxcod_cen while @@fetch_status=0 Begin select @importeVales=imp1 from tmpVales where cod_cen=@auxcod_Cen select @importeEfectivo=importe2 from tmpDiarioEfectivo where cod_cen=@auxcod_Cen select @importeTalones=importe2 from tmpDiarioTalones where cod_cen=@auxcod_cen select @importeTarjetas1=importe3 from tmpDiarioTarjetas where cod_cen=@auxcod_cen select @importeTarjetas2=importe1 from tmpDiarioSegundaForma where cod_cen=@auxcod_cen select @importeGastos=importe2 from tmpDiarioGastosTarjetas where cod_cen=@auxcod_cen
insert into detallecaja_aux (cod_cen,importe1) values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos) fetch next from cursortemporal into @auxcod_cen
Is it possible to check query execution plan of a store procedure from create script (before creating it)?
Basically the developers want to know how a newly developed procedure will perform in production environment. Now, I don't want to create it in production for just checking the execution plan. However they've provided SQL script for the procedure. Now wondering is there any way to look at the execution plan for this procedure from the script provided?
