Any help would be really appreciated....
My stored procedure...
CREATE PROCEDURE business3rd7
@Fromdate DATETIME,
@ToDate DATETIME
AS
select distinct CONVERT(VARCHAR(10),Receipts.Companynumber1)+CONVE RT(VARCHAR(10),Receipts.Companynumber2) as co ,
Receipts.Premium1+Receipts.Premium2 as Premium,
"CAN"=case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"NET"=Receipts.Premium1+Receipts.Premium2-case when Receipts.transactiontype='CAN'
then (receipts.premium1+receipts.premium2)
else 0
end,
"#NEW"=case when Receipts.transactiontype='NEW' then count(Receipts.policynumber) else
0 end,
-------
"$NEW"=
case when Receipts.transactiontype='NEW' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#REN"=case when Receipts.transactiontype='REN' then count(Receipts.policynumber) else
0 end,
"$RENEW"=
case when Receipts.transactiontype='REN' then (Receipts.Premium1+Receipts.premium2)
else 0 end,
"#AP"=case when Receipts.transactiontype='AP' then count(Receipts.policynumber) else 0
end,
"$AP"=
case when Receipts.transactiontype='AP' then (Receipts.Premium1+Receipts.premium2) else
0 end,
"#SENT"=case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) end,
"%"=case when case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end =0 then 0
when case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end=0 then 0
else
case when Receipts.transactiontype='REN' then count(Receipts.policynumber)else 0
end /case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(policy.policynumber) else 0 end end,
"Current Year"= case when policy.Renewalofferdate between @Fromdate AND @ToDate then
count(clubamount) end,
"Previous Year"=case when policy.Renewalofferdate between DateAdd(year, -1, @Fromdate)
AND DateAdd(year, -1, @ToDate) then count(clubamount) end,
"#AA"=count(receipts.clubamount),
"$AA"=sum(receipts.clubamount)
FROM Receipts,Policy
where Receipts.Agencyid=Policy.Agentid
group by
Receipts.CompanyNumber1,Receipts.CompanyNumber2,
Receipts.Premium1,Receipts.Premium2,
Receipts.TransactionType,policy.Renewalofferdate,
Receipts.Agencyid
I need help makeing the following query run more efficently.
Code:
SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip FROM Landlord_tbl t1 left outer join Mail_tbl t2 ON t2.potentialSitesID = t1.potentialSitesID WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip ORDER BY t1.firstName, t1.lastName, t1.city, t1.state
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
I recently installed SQL2005 Express on a Dell Precision workstation to be accessed by 6 users. The PC has XP Pro, 2GB memory, and 2.0GHz core2due CPU. This PC is dedicated for the database only. I also set XP Performance options in System Properties to Background services and System cache. Now, the users are experiencing slow response from the database. How can I make the database response faster? Thanks for your input.
Hi.We have stored procedure update specific table Each time it run it delete 5000- 6000 rows from table then insert 5000- 6000 rows with different information. It take up to 1 1/2 min execute.
1.Can force Sql server do not make entry for each insert and if yes would it increase speed of procedure ? 2. Is any other way increase speed of insert?
Hi, Here is the typical processing I do in Data Flow 1) read data file (data source) 2) do something with the data (e.g. derived column, script component, etc) 3) pass data as parameters into OLE DB command (stored proc) to take care of the rest.
Would it be faster if I split the data into say 10 "chunks" (using split transform). Each chunk will go to its own OLE DB command (10 OLE DB command). Now I will have 10 concurrent processes running the stored proc ?
BEGIN SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey) FROM tbl_SurgerySlot INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode) AND tbl_SurgerySlot.ExtractDate = @ExtractDate AND tbl_SurgerySlot.StartTime BETWEEN @DateFrom AND @DateTo AND tbl_SurgerySlotDescription.NormalBookable = 1 AND tbl_SurgerySlot.SurgerySlotKey NOT IN( SELECT tbl_Appointment.SurgerySlotKey FROM tbl_Appointment WHERE tbl_Appointment.ExtractDate = @ExtractDate AND tbl_Appointment.Deleted = 0 AND tbl_Appointment.Cancelled = 0 ) END ELSE
BEGIN IF @96hrPlusFlag = 1
SELECT @AppointmentsCount = COUNT(tbl_SurgerySlot.SurgerySlotKey) FROM tbl_SurgerySlot INNER JOIN tbl_SurgerySlotDescription ON (tbl_SurgerySlot.Label = tbl_SurgerySlotDescription.Label AND tbl_SurgerySlot.PracticeCode = tbl_SurgerySlotDescription.PracticeCode) AND tbl_SurgerySlot.ExtractDate = @ExtractDate AND tbl_SurgerySlot.StartTime >@DateTo AND tbl_SurgerySlotDescription.NormalBookable = 1 AND tbl_SurgerySlot.SurgerySlotKey NOT IN( SELECT tbl_Appointment.SurgerySlotKey FROM tbl_Appointment WHERE tbl_Appointment.ExtractDate = @ExtractDate AND tbl_Appointment.Deleted = 0 AND tbl_Appointment.Cancelled = 0 )
Any ideas how to make reports faster when returning lots fo rows? I know you would need to work on your sql query etc.. Or maybe cache it. But i'm thinking of having a kind of middle tier thing that sits between your sql database and the reports itself. Any ideas would be appreciated
Hi y'all, I've recently run a profiler on my code and following query took 7 seconds: SELECT TOP 10 UI, COUNT(UI) AS Expr1 FROM table WHERE (UI <> 'custom_welcome') GROUP BY UI ORDER BY COUNT(UI) DESC Is it possible to rewrite this so my code gets faster? It's also possible that it's due to the size of the table? Thanks in advance! I'll let you know how long your query takes :)
Hi! I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips?????
Question 1: In a stored procedure, One SELECT stmt is there & depending upon the @rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way???
Question 2:Can anybody give me few examples like this?????? I need them desparetly.
I have rewritten a stored procedure that consists of a single select that selects from a view. Essentially I combined the select in the view and the select in the sp into one select. I am now trying to determine if the new version is faster.
The estimated execution plan gives a ratio of 96% : 4% in favour of the new version when I run them together from a query window but when I try to time them I can't get a satisfactory result.
If I run each query once and display the difference between start and end time, they display 0. If I run each one 100, 200, etc times I get different results each time.
Can anyone give me the basics of speeding up reports that use queries or views or nested views? Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc. We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports. Thanks
Hi, when I execute the sql in DBTestArchive and then DBTest query analyzer , I found that the run time is shorter in DBTest. Can I make assumption that the query run time is shorter in DBTest if I select data from it and insert into another database? Thanks.
INSERT INTO DBTestArchive.dbo.tblVendorMasterArchive SELECT * FROM DBTest.dbo.tblVendorMaster
I have a query which will display the employee name who is handling maximum number of project. the query is
Code Snippet
select EmployeeDetails.Empid,
EmployeeDetails.FirstName + ' '+EmployeeDetails.LastName as EmpName,
count(*) as Number_Of_Projects from EmployeeDetails left outer join LUP_EmpProject
on EmployeeDetails.Empid= LUP_EmpProject.Empid group by EmployeeDetails.Empid,EmployeeDetails.FirstName,EmployeeDetails.LastName HAVING count(*)=(SELECT TOP 1 COUNT(EmpID)
FROM LUP_EmpProject GROUP BY EmpID ORDER BY 1 DESC)
where EmployeeDetails table contain empname and empid. LUP_EmpProject table contain empid and project id.
Can anybody help me in writing a better query than the above given?
I have a query which joins multiple tables. This query has suddenly begun to take up to 2 minutes to run (vs. 5-10 seconds previously).
No major change in number of records in the tables (currently about 220,000). When I remove the PK from one of the tables which then forces a tablescan, the query returns to running in 5-10 seconds. If I add the PK back, performance is back to 2 minutes.
Yesterday i face a strange SQL Server 2000 behaviour :-(
I had a query that was wrapped inside a stored procedure, as usual. Suddenly, the stored procedure execution time raised from 9 secs to 80.
So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs. Tried stored procedure again, and speed again set to 80 secs.
Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.
My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?
Let's say I have a table of users. Let's imagine there's two fields: username (PK), password
Now I need to authenticate a user against this table. What is the recommended approach? Is it better / faster to (1) SELECT * FROM [User] WHERE username = 'whatever' AND password='whatever' or (2) SELECT * FROM [User] WHERE username = 'whatever' and then in my code check that the record returned matched the password?
I apologize for the long post but I am trying to give as much information as I can about the steps I've taken to troubleshoot this.
We have a stored procedure that builds a sql statement and executes it using the Execute command. When I execute the stored procedure through query analyzer it takes close to 5 seconds to execute. When I print out the exact same statement and execute it directly in query analyzer as "raw sql", it takes 0.5 seconds - meaning it takes 10 times longer for the code to execute in the stored proc. I altered the stored proc to execute the printed sql instead of building but it still takes the full 5 seconds and there were no changes in the execution plan. This makes me confident that the issue is not caused by the dynamic sql. I've used with recompile to make sure that the stored procedure caches the most recent execution plan. When I compare the execution plans, the stored proc uses a nested loop whereas the raw sql statement uses a hash join. Seeing that, I added the hash hint to the stored proc and doing so brought down the execution time down from 5 secs to 2 secs but still the raw sql statement uses a clustered index whereas the stored proc uses a non-clustered index and that makes the statement 4 times slower. This proves how efficient clustered indexes are over non-clustered ones, but it doesn't help me since, as far as I know, I can't force SQL Server to use the clustered index.
Does anyone know why sql server is generating such an inefficient execution plan for the stored proc compared to the execution plan that it generates when executing the raw sql statement? The only thing I can think of is that some stats are not updated and that somehow throws off the stored proc. But then again, shouldn't it affect the raw sql statement?
No clue whats causing this error,please help Server Error in '/learn' Application.
Unable to open the physical file "g:inetpubwwwrootlearnApp_DataPersonal.mdf". Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".An attempt to attach an auto-named database for file g:inetpubwwwrootlearnApp_DataPersonal.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I have no idea what I'm doing in SQL Server 2000... honestly no DBMS. I have a book at home but am required to do a backup of about 20 databases today (updating software tonight). Can anyone help me out? I found this (http://www.databasejournal.com/img/backupdatabase.sql) script but do not know how to run it (query analyzer?).
hi i am having a hard time with two kinds of text files that have kind of 'repeating groups' in them...i want to loop it, but dont know how. one is a text file with a record length of 1200 bytes, but all 95601records are all on one row with no lf, cr or anything else between them, so i cannot feature how to get the forEach container to chop of a Right of claimchunk of 1200 bytes at a time, then go get the next 1200 bytes, because the items aren't stacked, they are adjacent to each other, if you see what i mean. the other text file has a record lenght of 52 bytes with 28 bytes filler, but this file also goes 'down and across', meaning that here, there are fourteen 'rows' in the file, and they have thousands of lines too, so this one also has to consume all the columns on the row before it moves to the next row. am i making this harder than it needs to be? thanks for any light
I need to get back the actual characters (without trailing blanks) contained in a char(43) field. So i tried: - substring(fieldname,1,len(fieldname)) which yields a 43 char column - using a variable that contains the actual length in substring(fieldname,1,@nchar)) the same. - tricking by concatenating the resulting string with a dummy like "" didn't work either. So apparently I am at a dead end. Anybody a clue, hint or solution???? Thanks to all contributors
Ok, I know my connection string and config file are good. Once the rest of the code is added I receive this error: "SQL Server does not exist or access denied. " I'm using the 1.1 framework and my hosting company provides MSSql 2005. Thanks for your time, Charlie Here's the code in my cond behind file. using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using System.Data.SqlClient;
namespace blog { /// <summary> /// Summary description for WebForm1. /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Data.SqlClient.SqlConnection sqlConnect; protected System.Web.UI.WebControls.DataList dList; protected System.Data.SqlClient.SqlDataAdapter DA; protected System.Data.SqlClient.SqlCommand sqlSelectCommand1; protected System.Web.UI.WebControls.LinkButton linkAdmin; protected System.Web.UI.WebControls.Image header; protected blog.DS ds1;
#region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); }
/// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.sqlConnect = new System.Data.SqlClient.SqlConnection(); this.DA = new System.Data.SqlClient.SqlDataAdapter(); this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand(); this.ds1 = new blog.DS(); ((System.ComponentModel.ISupportInitialize)(this.ds1)).BeginInit(); this.linkAdmin.Click += new System.EventHandler(this.linkAdmin_Click); // // sqlConnection String //////////////////webserver//////////////// this.sqlConnection1.ConnectionString = "Server=xxx.xxx.xxx.x;Database=myDataBase;User ID=myID;Password=myPass";
//////////////////local/////////////////// //this.sqlConnect.ConnectionString = "workstation id=HAL;packet size=4096;integrated security=SSPI;data source=HAL;pers" + //"ist security info=False;initial catalog=blog"; // // DA // this.DA.SelectCommand = this.sqlSelectCommand1; this.DA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "blogEntry", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("bodyID", "bodyID"), new System.Data.Common.DataColumnMapping("linkID", "linkID")})}); // // sqlSelectCommand1 // this.sqlSelectCommand1.CommandText = "SELECT id, wxID, dateID, titleID, bodyID, linkID FROM blogEntry ORDER BY id DESC"; this.sqlSelectCommand1.Connection = this.sqlConnect; // // ds1 // this.ds1.DataSetName = "DS"; this.ds1.Locale = new System.Globalization.CultureInfo("en-US"); this.Load += new System.EventHandler(this.Page_Load); ((System.ComponentModel.ISupportInitialize)(this.ds1)).EndInit();
There is a query which when executed in the grid mode(ctrl+d) takes approx 0.02 seconds(about 21,000 rows) But when I execute in the text mode, it takes about 0.40 seconds!! Why is this difference? Also, when the records from this table are read from a VB application, they are equally slow (as in the text mode!) Why is it so slow on the text mode & relatively faster in the grid mode? Has anyone got any idea on ‘Firehose’ style cursor ?(which may speed up access of data in the VB application)
Hi guys, I have a car_race table which has these fields
car_id int race_id int b_car_won varchar // can have 'y' or 'n'
I need to know if the car lost the 1st race but won the next race
And example of that table for car_id 1:
car_id 1 race_id 1 b_car_won 'NO'
car_id 1 race_id 2 b_car_won 'YES'
Now this is the tricky part, the database has some data integrity issues, so this can occur:
car_id 1 race_id 1 b_car_won 'NO'
car_id 1 race_id 3 b_car_won 'YES'
So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:
car_id 1 race_id 1 b_car_won 'NO'
car_id 1 race_id 3 b_car_won 'YES'
car_id 1 race_id 4 b_car_won 'YES'
If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.
I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?