Tuning An Application Statement With Bind Variables
Jul 20, 2005
Hi gurus,
I just started to look at a very slow-running SQL statement
generated by an application (Siebel). I spooled the SQL from the
application, replaced the bind variables by their values, and tuned
from the Query Analyser. But after awhile, I realized that the
statement using bind variables and the same statement using the values
instead of the bind variables often have completely different
execution plans! Is that normal? Can someone tell me how the SQL
Server treats bind variables. Don't worry about being too technical,
I'm an Oracle DBA/developer.
I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.
Does anyone know how I can do this in SQL Server?
For instance, I have 20,000 employees and I'm pulling info by SS#:
Select * from EmpTable where SS_number = [SSN]
Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?
HiI've heard 2 things recently, can I confirm if their true/false?(1) If you have a stored procedure and you want to optimise it you cancall exec proc1,you could also use define/set for each of the variables and copy thecode into query analyser,this then makes it easier to tune. However the optimiser worksdifferently for these variables than it does for variables passed intothe query via exec and will produce a less optimalplan(2) There is a different optimiser used in query analyser than thatused otherwise? A colleaguehad a problem where a stored procedure called from dotnet code wasrunning slowly butone run from query analyser via exec, with exactly the same arguments,was running quicklyta
hi!!!the following is the sql which is veri slow cos of the 'Not In' clause,would appreciate if u anyone can suggest any other way to bring aboutthe same resultSELECT Id, LOC, AGENCY, BATCHFROM tblRowsWHERE tblRows.AGENCY NOT IN (SELECT DISTINCT B.AGENCYFROM tblRows AS A, tblRows AS BWHERE A.LOC = "B"AND B.AGENCY = A.AGENCYAND B.BATCH = A.BATCH)tblRowsID LOC AGENCY BATCH1 B 1000 WAD2 R 1000 WAD3 B 1010 QAD4 B 1020 WAD5 R 1020 WAD6 R 1030 RRR7 I 1030 RRR8 V 1030 RRR9 B 1040 UIA10 R 1040 UIA11 I 1040 UIA12 V 1040 UIAthe subquery is to return the rows with LOC = B. the above query as thewhole should return the rows where LOC <> b and also must exclude rowsbelong to the LOC = B subset (that is for example the first two rowswith ID 1 and 2. the first row with ID 1 has LOC as B, Agency as 1000and BATCH as WAD. the second row with Id 2 has LOC as R as the sameAGENCY and BATCH as first row with ID 1 so is the subset of first row.similarly row with Id's 4 and 5.the above query must return the following the rows (that is we can saythe orphan rows which doesn't have LOC AS B nor belongs to the B'ssubset6 R 1030 RRR7 I 1030 RRR8 V 1030 RRRhope i am clear in my explanation and would appreciate if someone canpoint me in the right direction. the reason for posting this in msaccess group is because this is going to be a query in MS Access.regardsbala
I need to delete the following records (from enrollment_fact):
SELECT a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID" FROM "dbo"."ENROLLMENT_FEX2" b, "dbo"."LOCATION_DIM" c LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a on c."LOC_SID" = a."LOC_SID" WHERE b."LOC_KEY" = c."LOC_KEY" and a."DATE_SID" between b."MIN_DATE" and b."MAX_DATE"
This is the approach (excuse the misuse of the concat function, but you get the idea)
DELETE FROM "dbo"."ENROLLMENT_FACT" WHERE CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID") IN ( SELECT DISTINCT CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID" ) FROM "dbo"."ENROLLMENT_FEX2" b, "dbo"."LOCATION_DIM" c LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a ON c."LOC_SID" = a."LOC_SID" AND a."DATE_SID" BETWEEN b."MIN_DATE" AND b."MAX_DATE")
hiFor an unavoidable reason, I have to use row-by-row processing(update) on a temporary table to update a history table every day.I have around 60,000 records in temporary table and about 2 million inthe history table.Could any one please suggest different methods to imporve the runtimeof the query?Would highly appreciate!
Below is a simple UPDATE that I have to perform on a table that hasabout 2.5 million rows (about 4 million in production) This queryruns for an enourmous amount of time (over 1 hour). Both theChangerRoleID and the ChangerID are indexed (not unique). Is thereany way to performance tune this?Controlling the physical drive of the log file isn't possible at ourclient sites (we don't have control) and the recovery model needs tobe set to "Full".UPDATE CLIENTSHISTORY SET ChangerRoleID = ChangerID WHEREChangerRoleID IS NULLAny Help would be greatly appreciated!
I'm updating the name data in a large user database with the following UPDATE statement. The staging table was bulk loaded from a flat file and contains 10 million records. The production table (Recipients) contains 15 million records. This worked correctly but this single update statement took an entire ten hours to run which is way too long. While it was running the server was clearly 100% disk bound. CPU activity was near nothing. We've just upgraded RAM from 1GB to 2GB but we expect data sizes to grow significantly and we can't keep adding RAM. Absolutely nothing else is running on this server. Any ideas how I can optimize this?
UPDATE Recipients SET [First] = Stages.[First] , [Last] = Stages.[Last] FROM Stages INNER JOIN Recipients ON (Stages.UserName = Recipients.UserName AND Stages.DomainID = Recipients.DomainID) WHERE (CASE WHEN Stages.[First] IS NULL THEN 1 ELSE 0 END + CASE WHEN Stages.[Last] IS NULL THEN 1 ELSE 0 END) <= (CASE WHEN Recipients.[First] IS NULL THEN 1 ELSE 0 END + CASE WHEN Recipients.[Last] IS NULL THEN 1 ELSE 0 END)
Text execution plan. I've made small annotations with the % information from the graphical execution plan:
|--Clustered Index Update(OBJECT:([Recipients].[dbo].[Recipients].[PK_Recipients]), SET:([Recipients].[First]=[Stages].[First], [Recipients].[Last]=[Stages].[Last])) |--Top(ROWCOUNT est 0) |--Sort(DISTINCT ORDER BY:([Bmk1000] ASC)) 14% |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([Stages].[DomainID], [Stages].[UserName])=([Recipients].[DomainID], [Recipients].[UserName]), RESIDUAL:(([Recipients].[UserName]=[Stages].[UserName] AND [Recipients].[DomainID]=[Stages].[Domain 25% |--Clustered Index Scan(OBJECT:([Recipients].[dbo].[Stages].[IX_Stages]), ORDERED FORWARD) 61% |--Clustered Index Scan(OBJECT:([Recipients].[dbo].[Recipients].[PK_Recipients]), ORDERED FORWARD)
Everything I've heard on the subject suggests you change the index scans to index seeks. How do I do this?
Any other tuning advice is greatly appreciated.
Here are the exact statements I used to create the tables:
CREATE TABLE Recipients ( ID INT IDENTITY (1, 1) NOT NULL, UserName VARCHAR (50) NOT NULL, DomainID INT NOT NULL, First VARCHAR (24) NULL, Last VARCHAR (24) NULL, StreetAddress VARCHAR (32) NULL, City VARCHAR (24) NULL, State VARCHAR (16) NULL, Postal VARCHAR (10) NULL, SourceID INT NULL,
CREATE TABLE Stages ( ID INT NULL, UserName VARCHAR(50) NOT NULL, DomainID INT NULL, Domain VARCHAR(50) NOT NULL, First VARCHAR(24) NULL, Last VARCHAR(24) NULL, StreetAddress VARCHAR(32) NULL, City VARCHAR(24) NULL, State VARCHAR(24) NULL, Postal VARCHAR(10) NULL ) CREATE CLUSTERED INDEX IX_Stages ON Stages (DomainID, UserName)
I am trying to use two variables in my SQL statement to query an access database and then pass the results to a datagrid. Nothing shows up in my datagrid. I think that the syntax on my SQL statement is wrong. I am really not sure how to embed the variables, especially since there are two. I really need help with this, it is for work. The code is posted below. Thanks. Sub Search_Click( s as Object, e as eventArgs) Dim conLibrary As OleDbConnectionDim Category As StringDim Search_Field As StringDim dstResults As DataSetDim dadResults As OledbDataAdapterDim dtblBooks As DataTable conLibrary = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:ewpsychiatrylibrarydb.mdb")conLibrary.Open Category = ddlSearch.SelectedItem.textSearch_Field = txtSearch.textdadResults = New OledbDataAdapter("Select * From rec WHERE '"& Category"' "=" '"&Search_Field"'", conLibrary)dstResults = New Dataset()dadResults.Fill(dstResults, "Table1") dgrdResult.DataSource = dstResults.Tables(0).DefaultView dgrdResult.DataBind()conLibrary.closeServer.Transfer("results.aspx") end Sub
The below code connects to a Teradata table and downloads the record set into my excel spreadsheet. No problem. The issue is: using a WHERE clause in the SQL statementI have a form with three variables on the form, (one is a date field and requires a range; (1) DateFrom to DateTo), (2) StatusX and (3) ErrorTypeX. I need to use any combination of these variables to retrieve the desired record set. i.e. variables 1 and 2, or 2 and 3, or 1 and 3 etc. or none of the variables which would return all records.how to set up the variables in the SQL statement?
Dim conn As ADODB.Connection Set conn = New ADODB.Connection Dim recset As ADODB.Recordset Set recset = New ADODB.Recordset Dim cmdSQLData As ADODB.Command Set cmdSQLData = New ADODB.Command Dim RowCnt, FieldCnt As Integer
Hi Guys, I am trying to manipulate an SQL statement based on the return from a menu with auto postback enabled.Simply I am working with dates, so I will declare variables for today, next week, last week and so on.How do I then use this in the SQL? I assumed that if I declared the following Dim my_today As String = Format(Date.Now(), "dd/MMM/yyyy")Then in the SQL I used SELECT * FROM my_table item_date = @my_todayI would get a result, but I get an error. What is the right way to do this? Many thanksSteve
select DEPTNUMB,DEPTNAME from openquery(m1db2u,"select DEPTNUMB,DEPTNAME from Q.ORG where DEPTNAME=@deptname")
Server: Msg 7399, Level 16, State 1, Line 4 OLE DB provider 'MSDASQL' reported an error. [OLE/DB provider returned message: [IBM][CLI Driver][DB2] SQL0206N "@DEPTNAME " is not a column in an inserted table, updated table, or any table identified in a FROM clause or is not a valid transition variable for the subject table of a trigger. SQLSTATE=42703 ]
--This works fine UPDATE tblTmpLA SET MT1 = 2222 / 1000 FROM tblTmpLA WHERE ID = 1
This is the way I want to do it but
UPDATE tblTmpLA SET @vname = 2222 / 1000 FROM tblTmpLA WHERE ID = 1
I get the error that Disallowed implicit conversion from datatype 'int' to datatype 'varchar' I guess that the variable does not compile as the text that it holds or so it seems.
I want to put this code inside a WHILE loop where the @vname will change on each iteration.
I'm trying to execute an update statement in a stored procedure that includes a variable for a column and a variable for a conditional constraint. For example I want to execute the following UPDATE, but using variables:
Update table set flagcode = 'A' where Field1 < 100
the field flagcode is a varchar and the field Field1 is an int This basically how I have my code set up:
declare @flag as varchar(20) declare @lowrange as varchar(20)
set @flag = 'A' set @thefield = 'Field1'
exec('update table set Flagcode = ' + @flag + ' where '+@thefield+' < 100')
I get the error: "Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'A'."
iam trying to pass variable to a statement to grab data to from one DB table and pitch it in the same table in another DB base on evaluation like a where clause. but its not working what am i doing rong
here is the code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20), @TABLEDESC varchar(20), @DBO varchar(20) set @TABLEDESC = 'ClientComment' set @DBO = '.dbo.' set @BDFR = 'Commander' + @DBO + @TABLEDESC set @BDTO = 'Test_Commander'+ @DBO + @TABLEDESC set @EQID = '80_300_113' insert into @BDTO select from @BDFR where Eqid = @EQID
I have a cursor that goes through a table with the names of all the database in my server. So for each fetch, the cursor gets the name of a database and assign it to a variable, @databaseName, and try to do some queries from that database by using the command "USE @databaseName". But "USE" doesn't take the variable @databaseName; it is expecting a database name (i.e. USE master).
Here is my code:
Declare @databaseName varchar(50) Declare c_getDatabaseName CURSOR for SELECT name from tblDatabases OPEN c_getDatabaseName FETCH NEXT from c_getDatabaseName into @databaseName While @@FETCH_STATUS = 0
Hi all,I would like to replace the default directory location (c: emp) and thefilename (emails.csv) with variables like @FileDir and @FileName in thestatement below.SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft TextDriver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')However, my attempts have not been successful.Any ideas appreciated, and TIA.Greg
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express. I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL. If I hard code the values in the SQL statement it works fine. Can someone explain what I'm doing wrong accomplish this? My code is below. Thanks in advance. file.aspx <asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>" SelectCommand="SELECT * FROM [Table]" InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" > </asp:SqlDataSource> file.aspx.vb Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click Dim variable1 As String = FileUpload.FileName Dim variable2 As String = Date.Now Dim path As String = Server.MapPath("~/directory/) If FileUpload.HasFile = True Then Try SqlDataSource.Insert() FileUpload.PostedFile.SaveAs(path & _ FileUpload.FileName) End Try
SELECT * FROM Cards WHERE CASE @Type = 1111 THEN CardType = 1111 ELSE CardType = 2222 END
GO
I know that the part after WHERE is wrong. But what I would like to achieve is this:
if the @type variable equals 1111 then get alla the rows with that value in the CardType-column. The same if @type = 2222, and if @type is any other value, then choose all rows regardles of the CardType value.
I'm getting the error "No recepient is specified".
I have set up a dataflow from a select statement into a record set, then have that dataflow point to a ForEach group with a mail task in it. I have set up variables for the username and subjectline. So in the mail task I have no value in the To: line because I specify an "http://www.sqlis.com/59.aspx">http://www.sqlis.com/59.aspx exactly.
I couldn't figure out how to included screen shots.
In order to feed a fact table of a dwh from a staging table I'm using the MERGE statement in order to control insert and update operations. It is possible that the staging table has duplicate rows respect to the fields controlled in the merge condition:
When I run the first time the MERGE statement unwanted rows could be inserted in the fact table.
Does the MERGE statement allow to manage this case or do I need to filter data from the staging table before to write them into the fact table?
I get the below error on the event log of my application server which uses SQL database.
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.
The statement has been terminated.RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo.BaseManagedEntity", column 'BaseManagedEntityId'.The statement has been terminated..
Details: RuleId:a811dcbc-4c5b-d9de-592b-f01e17fc0e9a. HealthServiceId:a5f70248-b545-4d35-7c84-e7aa87610ee4. The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Alert_BaseManagedEntity". The conflict occurred in database "OperationsManager", table "dbo. BaseManaged Entity", column 'BaseManagedEntityId'.The statement has been terminated..
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
Hi all,I would like to do something I thought was simple but I can't seem to figure it out. I would like to bind the Text properties to two labels to the columns in an SqlDataSource, much the same as you can bind a listbox to a SqlDataSource.Is this possible?The reason why I'm trying it this way is because I originally used an OleDB connection and datareader, but it doesn't seem to work on our service providers server (keeps saying that it can't find the database) even though it works on the four other server's I've tried. It definitely connects to the database when I use the SqlDataSource on a listbox control, but it fails when I use the same connection string with the OleDB connection.Is this the best way to go about it, or should I persist with finding the OleDB/datareader (the service provider has been no help at all). Thanks.
