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?
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.
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
Hi:
I have thousands of rows that I have to retrieve for my report. When I run my main report it returns my data as a reasonable time.
When I run my sub report (by its own) again data are returned in reasonable time.
When I build the sub report in my main report it takes forever (I have to stop). Are there any tricks that I can use to make this run faster?
Thank you in advance
Bahram
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 ?
Am i misunderstanding concurrent process here ?
Thanks
Anyone got any alternatives to the SQL Statement below that would make it run a bit faster:
ALTER PROCEDURE sproc_ReturnAvailability
@ExtractDate DateTime,
@DateFrom DateTime,
@DateTo DateTime,
@96hrPlusFlag int,
@AppointmentsCount int OUTPUT
AS
IF @96hrPlusFlag = 0
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
)
END
Cheers...
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
GO
Query plan...
----------------------Query Plan
|--Sort(DISTINCT ORDER BY:([Expr1008] ASC, [Expr1009] ASC, [Expr1010] ASC, [Expr1011] ASC, [Expr1012] ASC, [Expr1013] ASC, [Expr1014] ASC, [Expr1015] ASC, [Expr1016] ASC, [Expr1017] ASC, [Expr1018] ASC, [Expr1019] ASC, [Expr1020] ASC, [Expr1021] ASC, [Ex
|--Compute Scalar(DEFINE:([Expr1008]=Convert([Receipts].[CompanyNumber1])+Convert([Receipts].[CompanyNumber2]), [Expr1009]=[Receipts].[Premium1]+[Receipts].[Premium2], [Expr1010]=If ([Receipts].[TransactionType]='CAN') then ([Receipts].[Premium1]+[R
|--Compute Scalar(DEFINE:([Expr1004]=Convert([Expr1076]), [Expr1005]=Convert([Expr1077]), [Expr1006]=Convert([Expr1078]), [Expr1007]=If ([Expr1078]=0) then NULL else [Expr1079]))
|--Stream Aggregate(GROUP BY:([Receipts].[CompanyNumber1], [Receipts].[CompanyNumber2], [Receipts].[Premium1], [Receipts].[Premium2], [Receipts].[TransactionType], [Policy].[RenewalOfferDate], [Receipts].[AgencyID]) DEFINE:([Expr1076]=COUN
|--Sort(ORDER BY:([Receipts].[CompanyNumber1] ASC, [Receipts].[CompanyNumber2] ASC, [Receipts].[Premium1] ASC, [Receipts].[Premium2] ASC, [Receipts].[TransactionType] ASC, [Policy].[RenewalOfferDate] ASC, [Receipts].[AgencyID] ASC))
|--Hash Match(Inner Join, HASH:([Policy].[AgentID])=([Receipts].[AgencyID]), RESIDUAL:([Policy].[AgentID]=[Receipts].[AgencyID]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Policy]))
|--Table Scan(OBJECT:([gasInquiry].[dbo].[Receipts]))
The two tables has number of records as 13349 and 97032.It taking more than 30 mins...
Any way to make it faster...
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
Dear VB coders!
I have 8.000 or 16.000 products on my list in an sql table. It takes 8 or 16 secs to find the last item.
How could I make the search faster? Indexing? But how?
I created:
createTable1.CommandText = "Create TABLE products (ID int IDENTITY(0,1) PRIMARY KEY, name ntext)"
and I use simple WHERE to search.
Thank you!
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?
Thanks in for you help in advance
I have a situation where currently, I check to see if a record exists and I do an update if it does, I do an INSERT if it does not. This happens about 200k times each night.
I'm trying to speed the application up that is doing this and I was wondering if it would be a better idea to just delete the records I'm about to recreate, before I start, then do an unconditional insert.
It would eliminate my IF EXISTS check but if an update is faster than an insert, it might even out.
Also, will SQL deal with the big delete and the data being recreated each night without too much bloating or fragmentation?
We intend to import up to 20+ GB of data into a database that will be a snapshot of month end data. The database tables must be dropped and then recreated just prior to importing the data. We are investigating BCP, DTS, and Bulk Insert. What is the fastest method for importing data given our approach?
View 1 Replies View RelatedHello,
What is faster / better for performance?
Check if something already excist and update the changed values? or delete the whole table and then insert everything?
Thanks!
Hallow
My code does not insert Data into Database, please can someone look on it and give a technical problem over here please
It does not generate any error please, when I CLICK THE BUTTON IT DOES NOT GENERATE ERROR, IT GIVE ME THE MESSAGE THAT ITEM ADDED, BUT WHEN I LOOK MY TABLE NOTHING ID INSIDE Sub Add_To_Cart(ByVal Src As Object, ByVal Args As EventArgs)
Dim FVProductID As Label = FormView1.FindControl("ProductID")
Dim FVProductName As Label = FormView1.FindControl("ProductName")Dim FVProductPrice As Label = FormView1.FindControl("ProductPrice")
Dim DBConnection As SqlConnection Dim DBCommand As SqlCommand
Dim sql As String
Dim SQLAddString As String
DBConnection = New SqlConnection("Data Source=MANDARISQLEXPRESS;Initial Catalog=SHOES;Integrated Security=True")
DBConnection.Open()
If Not Session("OrderID") Is Nothing Then
sql = "SELECT Count(*) FROM ShoppingCart " & _ "WHERE OrderID = '" & CType(Session("OrderID"), String) & "' " _
& "AND ProductID = '" & FVProductID.Text & "'"
DBCommand = New SqlCommand(sql, DBConnection)
If DBCommand.ExecuteScalar() = 0 Then
SQLAddString = "INSERT INTO ShoppingCart (OrderID, ProductID, OrderDate, ProductName, ProductPrice, ProductQnty) VALUES (" & _
"'" & CType(Session("OrderID"), String) & "', " & _"'" & FVProductID.Text & "', " & _
"'" & Today() & "', " & _"'" & FVProductName.Text & "', " & _
"'" & FVProductPrice.Text & "', 1)"DBCommand = New SqlCommand(SQLAddString, DBConnection)
DBCommand.ExecuteNonQuery()
End If
End If
DBConnection.Close()
Src.Text = "Item Added"Src.ForeColor = Color.FromName("#990000") Src.BackColor = Color.FromName("#E0E0E0")
Src.Font.Bold = True
End Sub
I have a scenario where two tables are in a One-to-Many relationshipand I need to move the data from the Many table to the One table sothat it becomes a One-to-One relationship.I need to salvage the records from the many table and without goinginto detail, one of the reasons I can't do the opposite asthere are records in the ONE table that I need to keep even if theydon't have any child records in the MANY table.Below I created the code to create the sample tables:1- tblProducts is the ONE side table2- tblProductDetails is the MANY side table3- tblProductsResult is the RESULT I expect to get after runningsome T-SQL code4- tblProductComponents is another MANY side table to tblProducts5- tblProductComponentsResult is the RESULT I expect to get...Some of the points to consider:6- Normally all UniqueID columns are to be IDENTITY. Forthis sample i am entering the UniqueID values myself.7- I don't want to create new tables like tblProductsResultand tblProductComponentsResult. I want to update the real tables.I have created the tblxxxResult tables only for this post.8- The goal is to update the name of the Product by giving it thename of the first matching Name from tblProductDetails.9- If there are more than one entry in tblProductDetails for eachProduct, then I need to create new Products inheriting the originalProduct's information including its child records from tblProductComponents.If you run the code and open the tables it will be much clearerto visually see what I want to achieve.CREATE DATABASE MyTestDBGOUSE MyTestDBGOCREATE TABLE [dbo].[tblProducts] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProducts VALUES (1, 'ABC', 55)INSERT INTO tblProducts VALUES (2, 'DEF', 66)INSERT INTO tblProducts VALUES (3, 'GHI', 77)INSERT INTO tblProducts VALUES (4, 'JKL', 88)CREATE TABLE [dbo].[tblProductDetails] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[ProductID] int) ON [PRIMARY]GOINSERT INTO tblProductDetails VALUES (1, 'ABC1', 1)INSERT INTO tblProductDetails VALUES (2, 'DEF', 2)INSERT INTO tblProductDetails VALUES (3, 'GHI', 3)INSERT INTO tblProductDetails VALUES (4, 'GHI2', 3)INSERT INTO tblProductDetails VALUES (5, 'GHI3', 3)INSERT INTO tblProductDetails VALUES (6, 'JKL2', 4)INSERT INTO tblProductDetails VALUES (7, 'JKL', 4)INSERT INTO tblProductDetails VALUES (8, 'JKL3', 4)INSERT INTO tblProductDetails VALUES (9, 'JKL4', 4)CREATE TABLE [dbo].[tblProductComponents] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponents VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponents VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponents VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponents VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponents VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponents VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponents VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponents VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponents VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponents VALUES (10, 4, 'JKLb')CREATE TABLE [dbo].[tblProductComponentsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[ProductID] int,[Component] [varchar] (80) NULL) ON [PRIMARY]GOINSERT INTO tblProductComponentsResult VALUES (1, 1, 'ABCa')INSERT INTO tblProductComponentsResult VALUES (2, 1, 'ABCb')INSERT INTO tblProductComponentsResult VALUES (3, 1, 'ABCc')INSERT INTO tblProductComponentsResult VALUES (4, 2, 'DEFa')INSERT INTO tblProductComponentsResult VALUES (5, 2, 'DEFb')INSERT INTO tblProductComponentsResult VALUES (6, 2, 'DEFc')INSERT INTO tblProductComponentsResult VALUES (7, 2, 'DEFd')INSERT INTO tblProductComponentsResult VALUES (8, 3, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (9, 4, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (10, 4, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (11, 5, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (12, 6, 'GHIa')INSERT INTO tblProductComponentsResult VALUES (13, 7, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (14, 7, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (15, 8, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (16, 8, 'JKLb')INSERT INTO tblProductComponentsResult VALUES (17, 9, 'JKLa')INSERT INTO tblProductComponentsResult VALUES (18, 9, 'JKLb')CREATE TABLE [dbo].[tblProductsResult] ([UniqueID] [int] NOT NULL PRIMARY KEY ,[Name] [varchar] (80) NULL,[TagNo] [int] NULL) ON [PRIMARY]GOINSERT INTO tblProductsResult VALUES (1, 'ABC1', 55)INSERT INTO tblProductsResult VALUES (2, 'DEF', 66)INSERT INTO tblProductsResult VALUES (3, 'GHI', 77)INSERT INTO tblProductsResult VALUES (4, 'JKL', 88)INSERT INTO tblProductsResult VALUES (5, 'GHI2', 77)INSERT INTO tblProductsResult VALUES (6, 'GHI3', 77)INSERT INTO tblProductsResult VALUES (7, 'JKL2', 88)INSERT INTO tblProductsResult VALUES (8, 'JKL3', 88)INSERT INTO tblProductsResult VALUES (9, 'JKL4', 88)I appreciate your assistance on this.Thank you very much
View 6 Replies View RelatedI am using INSERT into Vendors (.....) VALUES (....) form of insert statement. How can I make sure that the insert fails if thie new vendor's vendorname exists?
I cannot create a unique index on 'VendorName' column since it is more than 900 bytes and SQL Server will not allow to create index on a column bigger than 900 bytes.
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
View 6 Replies View Relatedhi all, if i have a comma delimited string and want to insert each delimited substring into a table which of the following way is faster?pass the whole string into the a stored procedure and loop through the delimited string and pick out the substring and insert into the table orloop and pass the substring into a stored procedure and insert N times?or any other better ways someone could suggest me to do thanks!
View 6 Replies View Relatedi have a query that i can use either and get the same results. i just need to shave some time off... which is faster the LIKE or IN () ???
thanks
kim
I was just wondering if this can be done any faster? code-wise that is...
Don't mind the converts, can't do without them, as the data discipline for the source table isn't always reliable, while I have to be absolutely sure the destination data ends in the required format.
UPDATE MATCH_basistabel
SET MATCH_basistabel.matchfelt = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.søgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n),
MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n2),
MATCH_basistabel.medlemsnummer = ALL_tbl_medlemsinfo.medlemsnummer,
MATCH_basistabel.nationalitet = convert(varchar(10), ALL_tbl_medlemsinfo.nationalitet),
MATCH_basistabel.organisationsnummer = convert(varchar(10),ALL_tbl_medlemsinfo.organisati onsnummer),
MATCH_basistabel.medlemskab = convert(varchar(20), ALL_tbl_medlemsinfo.medlemskab),
MATCH_basistabel.ipdn = ALL_tbl_medlemsinfo.ipdn,
MATCH_basistabel.ipdnroll = convert(varchar(20), ALL_tbl_medlemsinfo.ipdroll),
MATCH_basistabel.franavision = 1
FROM MATCH_basistabel, ALL_tbl_medlemsinfo
WHERE isnumeric(matchfelt) = 1
AND (convert(int, MATCH_basistabel.matchfelt) = convert(int, ALL_tbl_medlemsinfo.medlemsnummer)
AND MATCH_basistabel.franavision = 0)
Hi Guys,
I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...
Many Thanks,
Regards,
Shaffiq
I'm sonewhat new to MS SQL Server and I'm wondering about which of thefollowing two queries would be faster:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSELECT@ResidencesBuilt = SUM(CASE WHEN BuildingType = 0 THEN Built END),@BarracksBuilt = SUM(CASE WHEN BuildingType = 1 THEN Built END),@AirBaysBuilt = SUM(CASE WHEN BuildingType = 2 THEN Built END),@NuclearPlantsBuilt = SUM(CASE WHEN BuildingType = 3 THEN Built END),@FusionPlantsBuilt = SUM(CASE WHEN BuildingType = 4 THEN Built END),@StarMinesBuilt = SUM(CASE WHEN BuildingType = 5 THEN Built END),@TrainingCampsBuilt = SUM(CASE WHEN BuildingType = 6 THEN Built END),@FactoriesBuilt = SUM(CASE WHEN BuildingType = 7 THEN Built END)FROM BuildingsGROUP BY kdIDHAVING kdID = 2902Or:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSET @ResidencesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 0AND kdID = 2902)SET @BarracksBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 1 ANDkdID = 2902)SET @AirBaysBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 2 ANDkdID = 2902)SET @NuclearPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =3 AND kdID = 2902)SET @FusionPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 4AND kdID = 2902)SET @StarMinesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 5AND kdID = 2902)SET @TrainingCampsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =6 AND kdID = 2902)SET @FactoriesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 7AND kdID = 2902)The data source is:kdID BuildingType Built2902 6 02902 7 02902 4 02902 0 802902 2 02902 1 52902 3 402902 5 10Or:CREATE TABLE [dbo].[Buildings] ([kdID] [int],[BuildingType] [tinyint],[Built] [int])INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 0, 80)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 1, 5)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 2, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 3, 40)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 4, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 5, 10)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 6, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 7, 0)Analyzer says the first would be faster, but it has a lot of SUM()'s andwhatnot so I'm not too sure about this. There are also about 1000 rows inthe actual Buildings table. This will be a part of a stored procedure.
View 3 Replies View RelatedI want to know the # of users on our web site for each month in a given year. I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table! (My avg disk queue right now is above 7 and the query takes about 90 seconds).
Here's my current SP. Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.
USE [TNS]
GO
/****** Object: StoredProcedure [dbo].[Unique_Login_IPs] Script Date: 05/07/2007 12:38:52 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[Unique_Login_IPs]
(
@year1 int,
@year2 int
)
AS
BEGIN
SET NOCOUNT OFF;
-- Define the years for testing purposes
set @year1 = 2006
set @year2 = 2007
SELECT month,[2006] as y2006,[2007] as y2007
FROM
(
SELECT @year1 AS year, 1 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 1)) as tmpy1_1
UNION
SELECT @year1 AS year, 2 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 2)) as tmpy1_2
UNION
SELECT @year1 AS year, 3 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3
UNION
SELECT @year1 AS year, 4 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 4)) as tmpy1_4
UNION
SELECT @year1 AS year, 5 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 5)) as tmpy1_5
UNION
SELECT @year1 AS year, 6 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 6)) as tmpy1_6
UNION
SELECT @year1 AS year, 7 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 7)) as tmpy1_7
UNION
SELECT @year1 AS year, 8 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 8)) as tmpy1_8
UNION
SELECT @year1 AS year, 9 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 9)) as tmpy1_9
UNION
SELECT @year1 AS year, 10 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 10)) as tmpy1_10
UNION
SELECT @year1 AS year, 11 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 11)) as tmpy1_11
UNION
SELECT @year1 AS year, 12 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 12)) as tmpy1_12
UNION
SELECT @year2 AS year, 1 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 1)) as tmpy1_1
UNION
SELECT @year2 AS year, 2 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 2)) as tmpy2_2
UNION
SELECT @year2 AS year, 3 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 3)) as tmpy2_3
UNION
SELECT @year2 AS year, 4 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 4)) as tmpy2_4
UNION
SELECT @year2 AS year, 5 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 5)) as tmpy2_5
UNION
SELECT @year2 AS year, 6 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 6)) as tmpy2_6
UNION
SELECT @year2 AS year, 7 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 7)) as tmpy2_7
UNION
SELECT @year2 AS year, 8 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 8)) as tmpy2_8
UNION
SELECT @year2 AS year, 9 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 9)) as tmpy2_9
UNION
SELECT @year2 AS year, 10 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 10)) as tmpy2_10
UNION
SELECT @year2 AS year, 11 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 11)) as tmpy2_11
UNION
SELECT @year2 AS year, 12 AS month, COUNT(*) AS cnt
FROM (SELECT DISTINCT ipaddress
FROM servicelog AS servicelog_1
WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 12)) as tmpy2_12
) piv
PIVOT
(
SUM(cnt)
FOR year IN
([2006],[2007])
) as child
END
Hi all, I m new to this forum and this is my first question. I m having 2 pages in my web site ... page 1 query directly to db using sqldatasource, the second page query through a BLL then DAL by following the step in this tutorial (http://www.asp.net/learn/dataaccess/tutorial02vb.aspx?tabid=63).... Page 1 is using a "Like" query search and the Page 2 is the normal displaying some product detail.... Under normal circumstances, one will expect Page 1 will be way fastest than the Page 2... however the problem is Page 1 is in thunder speed while Page 2 takes 10 secs to load... 10 seconds is really not acceptable... I really couldnt figure out what happens... both Page 1 and Page 2 are using the same connection string which connection through a DSN.... How is the connection different by using sqldatasource and DAL?? Could someone please help.... ThanksP.S. I m using a Pervasive database
View 5 Replies View RelatedHi 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 :)
I have a cursor prcedure that is pretty slow because as the cursor moves through the data I have three select statement on the same table to find other rows information. Is there a better way to do this?
Simple Example of Code is:
DECLARE MyVARABLES 1 to X
DECLARE c1 CURSOR
FOR
SELECT MyData1, MyData2 to X
FROM MyTable
FOR UPDATE OF MyUpdateData
--Start Cursor
OPEN c1
FETCH NEXT FROM c1
INTO MyVariables
--LOOP
WHILE @@FETCH_STATUS = 0
BEGIN
-----------------------
-- Get other rows data to add to this rows data ......GUESSING THIS IS THE SLOW PART as the table is LARGESELECT MyVar1 = MyData1
FROM MyTable
WHERE MyTableColumns = MyVariables AND MyTableColumns2 <> MyVariables2 --FINDS OTHER ROW (I have three of these)
--Calculate & Update
If MyVarable = 'this or that'
BEGIN
UPDATE MyTable
SET MyUpdateData = MyVar1 * x *y WHERE CURRENT OF c1
END
-------------------
-- NextFETCH NEXT FROM c1
INTO MyVarables 1 to xEND
CLOSE c1
DEALLOCATE c1
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.
Thanx. Bye.
I´ve created a class to make some standard transaction development a little bit faster. The destructor seem to run, but something makes this object slow down the database, if SqlTransaction and/or SqlConnection isnt manualy handled with the method Commit(). Any ideas on how to handle the SqlTransaction and SqlConnection better?
public class DataTransaction
{
private bool blnError = false;
private ArrayList arrErrorList = new ArrayList();
private SqlConnectionobjConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());
private SqlTransactionobjTransaction;
public DataTransaction()
{
objConnection.Open();
objTransaction = objConnection.BeginTransaction();
}
~DataTransaction()
{
if(objTransaction.Connection != null)
{
objTransaction = null;
objConnection.Close();
}
}
public int ExecuteNonQuery(string Query)
{
int intRowsAffected = -1;
SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction);
try
{
intRowsAffected = objCommand.ExecuteNonQuery();
}
catch(Exception e)
{
blnError = true;
arrErrorList.Add(e.ToString());
}
finally
{
objCommand.Dispose();
}
return intRowsAffected;
}
public System.Object ExecuteScalar(string Query)
{
System.Object objToReturn = null;
SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction);
try
{
objToReturn = objCommand.ExecuteScalar();
}
catch(Exception e)
{
blnError = true;
arrErrorList.Add(e.ToString());
}
finally
{
objCommand.Dispose();
}
return objToReturn;
}
public bool Commit()
{
if(!blnError)
{
objTransaction.Commit();
return true;
}
else
{
objTransaction.Rollback();
return false;
}
objConnection.Close();
}
}
Hi,
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLE job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs. And I am talking about major improvement in terms of completion of the tasks here :)
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I'll appreciate comments. Thanks
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.
Is there anything I can do?
Hypothetically,
If I had a WHERE clause that had to compare a string to another string would it be faster one way or another if I broke it down to three different, smaller searches?
An example:
WHERE a = 'abc'
OR a = 'def'
OR a = 'ghi'
as opposed to:
WHERE a = 'abcdefghi'
ddave
Hi,
Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.
However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.
Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.
I'll appreciate comments. Thanks