|
I've been working on a query today which I've completed and can make run in about 7 seconds. However, when I create a stored procedure using the exact query code, the execution time increases to 103 seconds!
I'm sure there's a server setting or something like that that I've missed but it's been a long day already and I'm totally out of ideas!! Does anyone have any suggestions for things i can look at so I can go home?! 
Thanks in advance
It definitely isn't definatley
|
|
|
|
|
Run the SQL profiler when your procedure executes to see what it's doing. Then take the query(ies) that are identified in the profiler and run them manually - get the estimated execution plan to see what's happening.
|
|
|
|
|
Peter,
I've actually tried doing this (by including the actual excecution plan when running) but this still doesn't seem to elude any differences; each of the queries (20 in each) has approximately the same batch cost and the CPU costs are the same as well.
It's almost as if this query behaves differently when it's a stored procedure since that's the only variable I've changed. It's a SQL2000 server if that makes any difference and the query uses a few of temporary tables to do its calculations.
Any other ideas??
Cheers
Dave
It definitely isn't definatley
|
|
|
|
|
Just as a quick thought - you haven't got with recompile set on the stored procedure have you?
|
|
|
|
|
That is really odd. Have you tested a few times alternating between the sproc and the adhoc?
Both should pick up the same cached execution plan - and be ballpark the same speed (sproc should be slightly longer, but not noticibly). Of course the first one you run will have all the overhead of figuring out the execution plan and pulling the table indexes off disk...
|
|
|
|
|
Can you upload the stored procedure so we can take a look?
|
|
|
|
|
I don't think my client will allow that unfortunately - confidentiality and all...
It definitely isn't definatley
|
|
|
|
|
I m facing problem with sql procedure. i want to retrieve records those opening balance is < fromdate if a client open new account between my selected date range so open balance should be 0.00. becoz i m taking open balance < from date.
please help ..........
my store procedure ...
CREATE PROCEDURE LedgerStatement_RPT(
@FROMBOID CHAR(16),
@TOBOID CHAR(16),
@FROMDATE VARCHAR(10),
@TODATE VARCHAR(10),
@GROUPCODE VARCHAR(6)
)
AS
SET NOCOUNT ON
--EXEC LedgerStatement_RPT '1201980000003843','1201980000003843','08/01/2006','01/03/2007',NULL
--set @FROMBOID='1201980000003843'
--set @TOBOID='A001'
--set @FROMDATE = '08/01/2006'
--set @TODATE ='01/03/2007'
Declare @OpeningBalance decimal(15,3)
Declare @ClosingBalance decimal(15,3)
set @ClosingBalance =
(select IsNull(sum(Cr),0)
from Voucher
WHERE BOID between ISNULL(@FROMBOID,BOID) AND ISNULL(@TOBOID,BOID)
and CreatedOn Between ISNULL(@FROMDATE,CreatedOn) AND ISNULL(@TODATE,CreatedOn)) - (select IsNull(sum(Dr),0)
from Voucher
WHERE BOID between ISNULL(@FROMBOID,BOID) AND ISNULL(@TOBOID,BOID)
and CreatedOn Between ISNULL(@FROMDATE,CreatedOn) AND ISNULL(@TODATE,CreatedOn))
SELECT ISNULL(V.BOID,'') BOID,CM.FirstHolderName,IsNull(ISNULL(V.YearCode,'') +'/'+ cast(V.VNo as varchar),'') as BillNo,
IsNull((select BillDate from BillHeader
where BillHeader.DueDate= V.DueDate),V.CreatedOn) as BillDate,
IsNull(V.Narration,'') As Remarks,Dr,Cr,CM.BOGroupCode,ISNULL(X.OpeningBalance,0.00) AS OpeningBalance,ISNULL(Y.ClosingBalance,0.00) AS ClosingBalance
From Voucher V,ClientMain CM,
(select V.BOID,IsNull((sum(V.Cr)-sum(V.Dr)),0.00) as OpeningBalance
from Voucher V,ClientMain C
WHERE V.BOID between ISNULL(@FROMBOID,V.BOID) AND ISNULL(@TOBOID,V.BOID)
and C.BOID = V.BOID
and C.BOStatus = 1
and V.CreatedOn < ISNULL(CONVERT(DATETIME,@FROMDATE) + '23:59:59.998' ,V.CreatedOn)
group by V.BOID)X,
(select V.BOID,IsNull((sum(V.Cr)-sum(V.Dr)),0.00) as ClosingBalance
from Voucher V,ClientMain C
WHERE V.BOID between ISNULL(@FROMBOID,V.BOID) AND ISNULL(@TOBOID,V.BOID)
and C.BOID = V.BOID
and C.BOStatus = 1
and V.CreatedOn <= ISNULL(CONVERT(DATETIME,@TODATE) + '23:59:59.998',CreatedOn)
--and V.CreatedOn <= ISNULL(@TODATE,V.CreatedOn)
group by V.BOID)Y
where V.BOID=CM.BOID
AND V.BOID between ISNULL(@FROMBOID,V.BOID) AND ISNULL(@TOBOID,V.BOID)
AND V.CreatedOn Between ISNULL(CONVERT(DATETIME,@FROMDATE),V.CreatedOn) AND ISNULL(CONVERT(DATETIME,@TODATE) + '23:59:59.998',CreatedOn)
-- AND V.CreatedOn < ISNULL(@FROMDATE,V.CreatedOn)
-- AND V.CreatedOn > ISNULL(@TODATE,V.CreatedOn)
AND ISNULL(CM.BOGroupCode,-1) = ISNULL(@GROUPCODE,ISNULL(CM.BOGroupCode,-1))
AND V.BOID=X.BOID
AND V.BOID = Y.BOID
order by billdate
I am software programming engineering student
and i want to develop in this field so Please help me whenever I want ur support.
Thanks & Regards
Mohammad Faiz Siddiqui
|
|
|
|
|
mohd faiz wrote: @FROMDATE VARCHAR(10),
@TODATE VARCHAR(10),
Why are you not holding these as DATETIME or SMALLDATETIME?
mohd faiz wrote: so Please help me whenever I want ur support.
What about when we are able to give it. If you want help whenever YOU want it then you should look at paying for a support contract!
Upcoming FREE developer events:
* Developer! Developer! Developer! 6
* Developer Day Scotland
My website
|
|
|
|
|
Colin Angus Mackay wrote: If you want help whenever YOU want it then you should look at paying for a support contract!
Sounds good. I'll go and put a draft contract together then 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Hi friends,
Can any one help me in, How can i read the data from MSSQl server (Let us say at x database and all tables in that database) and then convert data in to a text format and so that i can save and upload that data into [b]MySql [/b]Data base.
I was following a procedure which is satisfactory for few tables but as the table number and size grows my procedure will become a tedious one.
Anyhow i will tell the procedure i am following
I created a DataIntegrationServices Project which is available in MSSQL2005 server Business Intelligence Development Studio.
There i created a dataflowtask where we can give the origin and destination files. Origin was a OLEDB connection to connect the database and destination was a FlatFile. In this way i can convert data from one table to the text format with delimiters we select.
But i need to convert some hundred tables, can any one help me in this regard.
I use C# language for developing my applications.
Can any one suggest me an alternative or enhance the same procedure by certain modifications.
Please don't suggest third party tools which i have to buy, i prefer working with code or free available tools.
Thank you in advance.
N.Raghavendran.
Raghavendran
|
|
|
|
|
You could use SQL Server Integration Services (SSIS) to achieve this. For each table, set up a task that writes the contents of that table out to an individual flat (text) file.
This site[^] has plenty of useful resources for SSIS.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Hi,
Can you tell me exactly the way in which i can proceed?
Setting up a task for each table in the sense how can i proceed?
Should i go for workflowfoundation? or manually select each table and do the process?
Is that not feasible implementing a loop?
Thanks
Raghavendran
|
|
|
|
|
I'm afraid I'm not an expect in SSIS. You probably can set up a package to iterate over tables in a database but I don't know how to do that. If there are not many tables in your database and the design is not likely to change, you could manually set up tasks to export each table. I suggest you read the SSIS documentation to find out what it is capable of doing.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I have a ms access database table with "ID" autonumber formatted to "AB"0001. It incremented automatically to AB0002 and so on.It works properly in Ms Access table. But when I wanted get the ID recordset to Datagrid it shows only 1, 2 and so on not the same format as i mentioned before.
Any solution
|
|
|
|
|
Make sure you are pulling the correct column. "ID" may be a reserved word, and when you query the table, you are pulling the reserved word (like rowid in O.r.a.c.l.e.) and not your column.
When you SELECT data from the table, can you Alias the column or quote it? It may address the issue.
-Alias
SELECT ID as 'RowIdentifier' FROM table
-Quote
SELECT [ID] FROM table
If you get stuck, you can always append the "AB0000" to the results before they are displayed to the user.
|
|
|
|
|
Hello everyone,
Is it possible to read the contents of a .bak (SQL backup) file via .NET?
Thanks and best regards..
.:: Something is Wrong ::.
|
|
|
|
|
You can read any file in .NET by using the classes in the System.IO namespace. Whether you can make sense of the data is another matter. Backup files aren't designed to be read outside of SQL Server, unless you know how the data is structured it won't make any sense. You can invoke code to backup/restore a SQL Server database from .NET, but trying to read a backup file yourself seems pointless.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Thanks for your reply, let's me explain why I need to read the backup file :
The SQL Server which I work with is at a remote location, and also I do not have full administrative rights. So :
1. While the server is at a remote machine, the server cannot recognize the backup file path I give, the file should be uploaded to the machine in which the server is running.
2. Even if this is done the server will not give me restore permission.
By the way, someone will send me backup files and I know the tables, fields etc. I just need to read field contents to a data table via a wrapper.
Regards..
.:: Something is Wrong ::.
|
|
|
|
|
As I have already stated, you can't read the backup file directly. You need to restore it. If you don't have permissions to restore the backup then you need to obtain them, otherwise you're stuck!
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Which is the best method to pass a value from DAL(Data access layer) to BAL(Business abstract layer) ? I used to pass DataSet/DataTable instances. But recently I heard, passing custom entities would be much good. But thinking in the performance perceptive, filling the custom entities after database operation will be time consuming. Is it ?
Anybody having any idea about DTO ? Is it possible to have a generalized DTO ?
|
|
|
|
|
N a v a n e e t h wrote: the custom entities after database operation done will be time consuming
You have to fill something with values from the database. A DataSet/DataTable still needs to be filled, so filling one as opposed to the other isn't an argument. Typically you will fill your custom object using a DataReader which is the fastest method for populating individual rows of data (google firehose cursor to find out why).
The key thing to understand is that a DataSet/DataTable is a convenience to make things easier for you as a developer. They do have tradeoffs though - for a start, because they are so generic, they have to be able to cope with a wide variety of data situations. As a consequence, they do tend to be very large. Secondly, they are MS specific constructs, so you are putting a large burden on external systems if they need to interact with yours - in other words, don't return a DataTable/DataSet over a web service.
|
|
|
|
|
Pete O`Hanlon wrote: in other words, don't return a DataTable/DataSet over a web service.
Thanks Pete. I am not speaking about webservices. I just want to know, "is it a good practice to pass DataSet/Reader/DataTable from Business layer to Data access layer" ? If it is not how this can be done efficiently ? I was doing a search for DTO which looks closer to this. But couldn't get enough information on DTO.
|
|
|
|
|
Well, DTO is basically a design pattern that is intended to remove the need to make multiple calls. Consider a sample where you want to get the name and address of a customer. One way of doing this would be to make two calls, with the first one getting the customer details and then the second one getting the address details. The other approach would be to return the customer and address information using one call.
As with everything else, there are tradeoffs that need to be considered. Is it more efficient to retrieve the two items in one go or is it better to get them one at a time? This could arise because you only need the address very occassionally, so the load to the server would be less with two calls. If you need the customer and address information all the time then you would be better off considering a DTO.
|
|
|
|
|
Thanks Pete for your help.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
