Why The Function And Batch With Same Sql Statement Have Different Performance?
Jan 3, 2008
hi all,
I have a function and batch witch consisted of same sql statement, and they will get the same result. but time they take is different, and produce a little different query plans, another significant difference is the estimated numbers. the function is always slower than the batch.
does anyone know why same sql would produce different query plans performance? how do i can to let the function as fast as the batch?
I built an SSIS package to pull a large amount data (over 4 million records) from our legacy system into a SQL server for analysis. As it stands the package takes somethign like 30+ hours to run. I would really like to trim down the time it is taking. The process runs monthly.
We're connecting to a cache database via an ODBC connection and dumping to an OLEDB connection to the SQL server. Is there something I can change in the rows per batch, maximum commit size to improve performance. It looks like the ODBC connection returns chunks of about 3000 rows at a time.
Is anyone familiar with cache that might know where I can get the best performing ODBC driver from?
What about the table that I'm dumping the data to? I assume no (or very few) indexes is the way to go for speed of dropping the data into the table.
I don't feel that 30 hours is an acceptable amount of time for this process to run. Am I off base here?
I have written the following code: Â SET NOCOUNT ON DECLARE @RowCount int; SET @RowCount = 0; Begin Try Begin Transaction -------------------------------------------------------- -----Table Name: AlertsStaticRecord_Archive -----Column Name: AlertID -------------------------------------------------------- ALTER TABLE Â [AlertsStaticRecord_Archive] Â ALTER COLUMN [AlertID] int NOT NULL;
[Code] .....
But, when I execute these batch, I am getting error:
Msg 8111, Level 16, State 1, Line 11 Cannot define PRIMARY KEY constraint on nullable column in table 'AlertsStaticRecord_Archive'. Msg 1750, Level 16, State 0, Line 11 Could not create constraint. See previous errors.
Because, the first ALTER statement is not getting executed.
The following exception is thrown with sqljdbc.jar (not with jtds0.9.jar)
com.microsoft.sqlserver.jdbc.SQLServerException: New request is not allowed to start because it should come with valid transaction descriptor. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection$1ConnectionCommand.doExecute(Unknown Source) at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source) at org.apache.commons.dbcp.DelegatingConnection.rollback(DelegatingConnection.java:265) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.rollback(PoolingDataSource.java:288)
While using the query :
if not exists ( select 1 from sysindexes where id = object_id('aaa') and name = 'aaa_pk')create unique nonclustered index aaa_pkon aaa(id)using Statement.executeBatch()at conection.commit()
Sample Code
conn = getConnection();
// create the statement and execute the query Statement stmt = null; try { conn.setAutoCommit(false); stmt = conn.createStatement(); for ( String sql : sqlList ) { stmt.addBatch( sql ); }
results = stmt.executeBatch(); stmt.clearBatch(); conn.commit(); //throws the exception } catch ( SQLException e ) { try { conn.rollback(); } catch ( SQLException e1 ) { throw new DataSourceException( e1 ); } throw new DataSourceException( "Error executing sql: %1", e, sqlList.toString() ); }
Is there a way to create a Batch file that will run an Update Statement and schedule it to run?I've used bcp to extract data to a txt file before, but i'm not sure if an Update can be performed.I'm using SQL Server 2008 R2 Express Edition so i don't have Server Agent available.
can anyone explain to me why the code excerpt 1 performs 60 reads on my DB, and code excerpt 2 performs 140000 ?
I know that specifically the statements are doing different things but they are both inserting into tables based on input parameters.
All relevant fields are indexed so I wouldn't have thought this was the issue?
Does the number of joins really make such a difference to performance?
code excerpt 1 (60 reads) INSERT INTO @table_var SELECT dbo.Organisation.OrganisationName, dbo.Organisation.DepartmentName, dbo.Address.BuildingNumber, dbo.BuildingName.BuildingName, dbo.SubBuildingName.SubBuildingName, Thoroughfare_1.ThoroughfareName AS DependentThoroughfareName, ThoroughfareDescriptor_1.ThoroughfareDescriptor AS DependentThoroughfareDescriptor, dbo.Thoroughfare.ThoroughfareName, dbo.ThoroughfareDescriptor.ThoroughfareDescriptor, dbo.Locality.DoubleDependentLocality, dbo.Locality.DependentLocality, dbo.Locality.PostTown, dbo.Address.Outcode, dbo.Address.Incode, dbo.Address.ConcatenationIndicator FROM dbo.Address INNER JOIN dbo.BuildingName ON dbo.Address.BuildingNameKey = dbo.BuildingName.BuildingNameKey INNER JOIN dbo.Locality ON dbo.Address.LocalityKey = dbo.Locality.LocalityKey INNER JOIN dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND dbo.Address.PostcodeType = dbo.Organisation.PostcodeType INNER JOIN dbo.SubBuildingName ON dbo.Address.SubBuildingNameKey = dbo.SubBuildingName.SubBuildingNameKey INNER JOIN dbo.Thoroughfare ON dbo.Address.ThoroughfareKey = dbo.Thoroughfare.ThoroughfareKey INNER JOIN dbo.ThoroughfareDescriptor ON dbo.Address.ThoroughfareDescriptorKey = dbo.ThoroughfareDescriptor.ThoroughfareDescriptorK ey INNER JOIN dbo.Thoroughfare Thoroughfare_1 ON dbo.Address.DependentThoroughfareKey = Thoroughfare_1.ThoroughfareKey INNER JOIN dbo.ThoroughfareDescriptor ThoroughfareDescriptor_1 ON dbo.Address.DependentThoroughfareDescriptorKey = ThoroughfareDescriptor_1.ThoroughfareDescriptorKey WHERE (dbo.Address.AddressKey = @addresskey) AND (dbo.Address.OrganisationKey = @organisationkey) AND (dbo.Address.PostcodeType = @postcodetype)
code excerpt 2:
INSERT INTO @table_var_out SELECT dbo.Organisation.OrganisationName, dbo.Address.OrganisationKey, dbo.Address.AddressKey, dbo.Address.PostcodeType FROM dbo.Address INNER JOIN dbo.Organisation ON dbo.Address.OrganisationKey = dbo.Organisation.OrganisationKey AND dbo.Address.PostcodeType = dbo.Organisation.PostcodeType WHERE (dbo.Address.Outcode = @outcode) AND (dbo.Address.Incode = @incode)
i have column in table which contains tabs and " i want replace with space...i am using repalce function is thier other way to improve performance with out using replace function.
The function is basically a set of Case Statements and various calculations dependant upon the Case.
Is this the best (performance wise) way to do it or should I dump the needed info in a Temp Table and do the calcs on it and then tie the select statement to the table.
I've seen both approaches done, but they both seem to be a different way of getting to the same conclusion. I'm just wondering which puts the lightest load on the server.
I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 containt approx. 6000 row and Table2 containt only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone".
When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes.
When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1 like in the following query : select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905'))) the query take an execution time of ~15 secondes!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.
It’s weird because, Sql Express 2005 is supposed to be more performant than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!
What is better for performance, using a user defined function or a sub query to perform an aggregate calculation in a select statement. for eg would it be best to call a user defined function which performs the below calculation of the sub query shown. I'm speaking purely from a performance point of view.
SELECT o.ordersid,
o.orders,
(SELECT SUM(i.total) FROM items AS i WHERE i.ordersid = o.ordersid) AS [total]
I am using SQL2005 EE with SP1. The server OS is windows 2K3 sp2
I have a table-valued function (E.g. findAllCustomer(Name varchar(100), gender varchar(1)) to join some tables and find out the result set base the the input parameters.
I have created indexes for the related joinning tables.
I would like to check the performance of a table-valued function and optimize the indexing columns by the execution plan.
I found the graphic explanation only show 1 icon to represent the function performance. I cannot find any further detail of the function. (E.g. using which index in joinning)
If I change the function to stored procedure, I can know whether the T-SQL is using index seek or table scan. I also found the stored procedure version subtree cost is much grether that the table-valued function
I would like to know any configureation in management studio can give more inform for the function performance?
When I am executing a query that uses a mathematical function on values from 2 tables the query takes much longer than the same query that uses values from 1 table, even though the join remains the same.
Why is this happening? Is there a way to bypass this problem?
Long query ( values from 2 tables ) : SELECT MAX ( ( SIGN ( attribute.keyValue- ( -2027587559 ) ) *SIGN ( attribute.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal FROM DATA data, ATTR attribute, TREE_ELEMENT elm, TREE_ELEMENT subject WHERE data.elmId=elm.id AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 ) AND subject.id=elm.subjectId AND subject.name = ‘test’
Short query ( values from 1 table ) : SELECT MAX ( ( SIGN ( data.keyValue- ( -2027587559 ) ) *SIGN ( data.keyValue- ( -2027587559 ) ) -1 ) *-1*data.val ) AS maxVal FROM DATA data, ATTR attribute, TREE_ELEMENT elm, TREE_ELEMENT subject WHERE data.elmId=elm.id AND attribute.keyValue IN ( 345647222,1569153803,1569146115,-2027587559 ) AND subject.id=elm.subjectId AND subject.name = ‘test’
Hi, I am trying to write a table-valued function in SQL Server 2005 (SP1) to return all active directory groups a user belongs too, using managed code (VB.NET).
Testing the code with a simple winform I get the list of groups in about 0.4 seconds. However the table-valued function takes upwards of 17 seconds to run! Is this normal for managed code in SQL Server?
Imports SystemImports System.TextImports System.DataImports System.Data.SqlClientImports System.Data.SqlTypesImports System.CollectionsImports System.DirectoryServicesImports Microsoft.SqlServer.ServerPartial Public Class UserDefinedFunctions#Region "Constants" ''' <summary> ''' The connection string for Active Directory. ''' </summary> 'Private Const LDAP_CONNECTION_STRING As String = "LDAP://<My LDAP connection string> ''' <summary> ''' The LDAP search filter need to find a user in Active Directory. ''' </summary> 'Private Const LDAP_SEARCH_FILTER_USER As String = "(&(objectclass=user)(objectcategory=person)(sAMAccountName={0}))"#End Region ''' <summary> ''' Gets all active directory groups for the user. ''' </summary> ''' <returns>All dataset permissions for the user.</returns> <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, FillRowMethodName:="udfUserActiveDirectoryGroupsFill", TableDefinition:="GroupID NVARCHAR(100)")> _ Public Shared Function udfUserActiveDirectoryGroups(ByVal userName As String) As IEnumerable ' Setup the active directory search. Dim searcher As New DirectorySearcher(LDAP_CONNECTION_STRING) searcher.Filter = String.Format(LDAP_SEARCH_FILTER_USER, userName) searcher.SearchScope = SearchScope.Subtree searcher.PropertiesToLoad.Add("distinguishedname") ' Run the active directory search. Dim result As SearchResult = searcher.FindOne() Dim userEntry As DirectoryEntry = result.GetDirectoryEntry() Dim userGroups As New ArrayList GetActiveDirectoryGroupsForEntry(userEntry, userGroups) Return userGroups End Function Public Shared Sub udfUserActiveDirectoryGroupsFill(ByVal source As Object, ByRef GroupID As SqlChars) GroupID = New SqlChars(CType(source, String)) End Sub ''' <summary> ''' Recursively gets the active directory groups for the directory entry. ''' </summary> ''' <param name="entry">The active directory entry.</param> ''' <param name="groups">The list of groups.</param> Private Shared Sub GetActiveDirectoryGroupsForEntry(ByVal entry As DirectoryEntry, ByVal groups As ArrayList) For i As Integer = 0 To entry.Properties("memberOf").Count - 1 Dim memberEntry As New DirectoryEntry("LDAP://" + entry.Properties("memberOf")(i).ToString()) groups.Add(memberEntry.Properties("sAMAccountName")(0).ToString()) GetActiveDirectoryGroupsForEntry(memberEntry, groups) Next End SubEnd Class
Hi,I have an UPDATE statement which runs very slow (about 1-2 minutes) inWeb as well as in Query Analyzer. Very plain UPDATE statement; usesindexes, etc. I though the reason might be the table being unorganizedbecause of the row expansions due to updating an existing column valuewith a larger value. Therefore, I ran a maintanenance plan whichreorganizes data and rebuilds indexes. But, it seems that there is noimprovement.Any thought or advise will be greatly appreciated.Sincerely,Pelin Bali.
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've just inherited a system and have some concerns about the speed ofconnections to a remote server (SQL2000). If I do a simple selectstatement on the table below, it takes 14 minutes to retrive 6 millionrows across a 2Mb line. Obviously it's a reasonable amount of data toretrieve, but I would have thought this would be quicker if I'm honest.Run locally, this is 50 seconds.My thoughts are that there may be some issues with our connection (weget general network errors sporadically, which are being looked at),but wanted some thoughts if the performance is acceptable for what itis doing with what is available. I don't think there is a SQL issue,but want to check if this sounds about right.It's early days, so I'm after a general impression of the speed ofretrieval for the amount of data on the available bandwidth. Assuming abest performance scenario, what is the minimum time it should take as abest guess ?ThanksRyanCREATE TABLE [FIELD_VALUES] ([DEALER_DATA_ID] [int] NOT NULL ,[FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[FIELD_VALUE] [numeric](15, 5) NULL ,[CHANGED_TYPE] [int] NULL ,CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED([DEALER_DATA_ID],[FIELD_CODE]) WITH FILLFACTOR = 90 ON [PRIMARY]) ON [PRIMARY]GO
I need to figure out the correct update statement syntax for the following integration.
I have a "Performance Table" which i insert weekly performance numbers into for each store. The table is constructed w/ columns such as Store, Weekenddate, Sales, Refunds, #ofPatients
In a "Averages Table" i have every weekenddate for each store populated. So 52 Weeks for 10 stores = 520 Rows of Store numbers & WeekendDates.
What i would like to do is run a loop or update statement which would update the store average for each weekendate based on the last 13 weeks.
This is my query
update performancestore_avgs set SalesAvg = (select sum(SalesHit)/Count(Store) from performance_store where performance_store.weekenddate >= performancestore_avgs.weekenddate-84 and performancestore_Avgs.store = performance_store.store)
The update statement runs but the averages are completely wrong.
I am using the Instr(), Len(), and Mid() function in my SQL query and I keep getting errors stating those are not recognized functions. IS this correct? are there any equivelants?
In MS Access, I could write a function in a module, then just call that function as part of the SQL statement. For example, "SELECT RemoveDashes([SS_No]) AS SSN FROM Employee" with "RemoveDashes" being the name of the function.
I'm trying to do the same with an asp.net page and sql server. I have a custom function in the code behind that I call in the SQL statement, but I get the error, "not a recognized function name".
What do I need to do to make this work? All help is greatly appreciated!
I need to know if there's any way to call a VB function from within an SQl statement. We have text in Rich Text format in a database and need it converted to regular text before we are able to perform searches on the data. Maybe I can use a stored procedure to accomplish this conversion or to call a function that would do this? Any help would be appreciated.
I want to run a much larget SQL statement, but for examples sake this is a good starting point
Code: Select efName, elName, eAddress, SUM(Convert(money, bonus1)+Convert(money, bonus2)+Convert(money, bonus3)) As TotalBonus, ePay FROM tableEInfo
It is telling me that I have to use Group By, but the problem is that most of my fields are text fields, which it looks like have to be converted in order to use with a group by statement. Is it possible to use the sum function with no group by statement?
CREATE FUNCTION GetPerson (@SSN integer, @NamePrefix varchar(10), @FirstName varchar(30), @MiddleName varchar(30), @LastName varchar(40), @NameSuffix varchar(10), @HomeID integer, @MailID integer, @DOB timestamp, @Gender varchar(1), @MaritalStatus varchar(1)) RETURNS integer as BEGIN DECLARE @PersonID integer set @PersonID=0 if @SSN>0 and @SSN<999999999 Begin select Min(lngPersonID) AS PersonID from Persons where lngSSN=@SSN End ELSE if @SSN is not null BEGIN IF @LastName is not null and @FirstName is not null and @MiddleName is not null and @NamePrefix is not null and @NameSuffix is not null Begin select MIN(lngPersonID) AS PersonID from Persons where strNamePrefix= @NamePrefix and strFirstName=@FirstName and strMiddleName=@MiddleName and strLastName=@LastName and strNameSuffix=@NameSuffix and lngSSN=@SSN End ELSE if @LastName is not null BEGIN select MIN(lngPersonID) as PersonID from Persons where strLastName=@LastName and lngSSN = @SSN END END return (@personID) END
I m having problem with the "Select" function the error I m getting is Select statements included within a function cannot return data to a client (error 444)
Cann I use "select" statement in the function? If not what is the alternative?
I reduced the size of the sproc because it is a big one, I donn have any proble with syntax.
I want to count how many occurences their is of each date that is returned by my sql query. I am not sure how to add the aggregate function code to my query I know how to just tell it to count all records, but not to tell it to count for each group of dates. For example I want it to count how many times 5/6/08 shows up in the returned results and so on. Here is my query I currently have. Any help would be greatly appreciated! Thanks!
The enc_timestamp is my date field.
Select a.template_id, a.enc_timestamp, a.created_by, b.first_name, b.last_name, b.last_name +', ' + b.first_name as fullname From template_audit a Join user_mstr b on a.created_by = b.user_id GROUP BY a.template_id, a.enc_timestamp, a.created_by,b.first_name, b.last_name Having a.template_id IN (543,3172,3031,3030,3134,3135,3171,1401,1937,3985,3173,2320,57,849,1775,1400,1747,3695,3957,3750,3954,3027,3241) ORDER BY a.enc_timestamp, b.first_name, b.last_name;
how should i use replace function in sql statement??
in my scenario, user enters the meeting ID and i want that if user enters the meeting ID which includes apostrophe('), the stored procedure should remove the apostrophe before inserting it into database table.
my stored procedure accepts two parameters. Is there any way to solve my problem??
I've got a complex UDF I need to call twice in a select statement, as shown below:
SELECT
dbo.myFunction(colName), dbo.myFunction(colName) * 2 FROM
tableName
The problem is, the result of "dbo.myFunction(colName)" is not being cached, so the function is executed again for "dbo.myFunction(colName) * 2". This is having a significant impact on performance (doubling the amount of time it takes for the query to execute).
Is there any way I can write the query so that dbo.myFunction is only executed once?
WHEN 1 THEN (Select name From Table2 where Table2.Code=Table1.Code) WHEN 2 THEN (Select name From Table3 where Table3.Code=Table1.Code) WHEN 3 THEN (Select name From Table4 where Table4.Code=Table1.Code) END
FROM Table1
Do I need to use Function instead of CASE for better performance ?
I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.