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.
Right now, a client of mine has a T-SQL statement that does thefollowing:1) Create a temp table.2) Populate temp table with data from one table using an INSERTstatement.3) Populate temp table with data from another table using an INSERTstatement.4) SELECT from temp table.Would it be more efficient to simply SELECT from table1 then UNIONtable 2? The simply wants to see the result set and does not need tore-SELECT from the temp table.
This function, F_TEMP_TABLE_EXISTS, checks for the existence of a temp table (## name or # name), and returns a 1 if it exists, and returns a 0 if it doesn't exist.
The script creates the function and tests it. The expected test results are also included.
This was tested with SQL 2000 only.
if objectproperty(object_id('dbo.F_TEMP_TABLE_EXISTS'),'IsScalarFunction') = 1 begin drop function dbo.F_TEMP_TABLE_EXISTS end go create function dbo.F_TEMP_TABLE_EXISTS ( @temp_table_name sysname ) returns int as /* Function: F_TEMP_TABLE_EXISTS
Checks for the existence of a temp table (## name or # name), and returns a 1 if it exists, and returns a 0 if it doesn't exist.
*/ begin
if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U')and o.id = object_id( N'tempdb..'+@temp_table_name ) ) begin return 1 end
return 0
end go print 'Create temp tables for testing' create table #temp (x int) go create table ##temp2 (x int) go print 'Test if temp tables exist'
select [Table Exists] = dbo.F_TEMP_TABLE_EXISTS ( NM ), [Table Name] = NM from ( select nm = '#temp' union all select nm = '##temp2' union all select nm = '##temp' union all select nm = '#temp2' ) a
print 'Check if table #temp exists'
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1 print '#temp exists' else print '#temp does not exist'
print 'Check if table ##temp4 exists' if dbo.F_TEMP_TABLE_EXISTS ( '##temp4' ) = 1 print '##temp4 exists' else print '##temp4 does not exist' go
-- Drop temp tables used for testing, -- after using function F_TEMP_TABLE_EXISTS -- to check if they exist.
if dbo.F_TEMP_TABLE_EXISTS ( '#temp' ) = 1 begin print 'drop table #temp' drop table #temp end
if dbo.F_TEMP_TABLE_EXISTS ( '##temp2' ) = 1 begin print 'drop table ##temp2' drop table ##temp2 end
Test Results:
Create temp tables for testing Test if temp tables exist Table Exists Table Name ------------ ---------- 1 #temp 1 ##temp2 0 ##temp 0 #temp2
(4 row(s) affected)
Check if table #temp exists #temp exists Check if table ##temp4 exists ##temp4 does not exist drop table #temp drop table ##temp2
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?
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
I am trying to create a report that sums up new cases weekly based on the previous 5 months. I have included some sample data below. The only field that displays a date is called monthSubmitted, varchar 7 and not very useful. How can I first convert this firld to a date ddmmyy?
How would you the code it so the user can run the report on demand at any given time and display the data for the last 5 months on a weekly basis? I am not sure as to how the date calculations should be coded.
I have been researching some performance problems in a very large application and I have a couple of questions about temp tables. (SQL 7.0 SP2)
I have one large procedure that I have been using as a test case. Originally this procedure was a cursor with lots of processing steps involving writing to, reading from and deleting in temp tables inside the cursor. I remember reading that temp tables inside a cursor were a potential performance problem, so I rewrote the procedure, replacing the cursor with a While Loop.
Doing this showed no increase in performance. Since Profiler was showing .5 second duration times on statements in the procedure accessing the temp tables I tested some more. I moved all the create statements to the top of the procedure, as I know these statements after processing steps can cause recompiles to happen. Still no performance increase.
Finally I replaced all the temp tables with actual tables, just to see what would happen. With no other changes the performance increased by more than 500%.
Can someone give me some clues as to what is happening here, because if this is a symptom of something I don't understand, the potential performance problems from other places where temp tables are similarly used in the application are enormous.
I am writing a set of functions and then a stored procedure to allow me to view some data in Reporting Services. I have written the first part of the function as shown ;
WHERE (datadate = @dt_src_date) AND (siteref = @vch_site_ref)
RETURN
END
I have then written a stored procedure which picks up the result set from the function and ultimately will deliver it to Reporting Services. The problem I have is that as soon as I run the CREATE PROCEDURE script, I get an error saying ;
Msg 216, Level 16, State 1, Procedure spWTRWeeklyTerrierData, Line 16
Parameters were not supplied for the function 'fnWTRTerrierData'.
How can I add parameters to the function sufficently so that I can run the Create procedure element of my code?
I am running A View that INSERTS into #Temp Table - On Only Certain Days the INSERT Speed into #tempDB is so slow. Â Attached snapshot that shows after one minute so many few records are inserted - and it dosent happen every day somedays its very fast.Â
I think this is a very simple question, however, I don't know the answer. What is the difference between a regular Temp table and a Global Temp table? I need to create a temp table within an sp that all users will use. I want the table recreated each time someone accesses the sp, though, because some of the same info may need to be inserted and I don't want any PK errors.
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.
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 am inserting something into the temp table even without creating it before. But this does not give any compilation error. Only when I want to execute the stored procedure I get the error message that there is an invalid temp table. Should this not result in a compilation error rather during the execution time.?
--create the procedure and insert into the temp table without creating it. --no compilation error. CREATE PROC testTemp AS BEGIN INSERT INTO #tmp(dt) SELECT GETDATE() END
only on calling the proc does this give an execution error
Simple example:    declare @tTable(col1 int)    insert into @tTable(col1) values (1)    select * from @tTable
Works perfectly in SQL Server Management Studio and the database connection is OK to as I may generate PP table using complex (or simple) queries without difficulty.
But when trying to get this same result in a PP table I get an error, idem when replacing table variable by a temporary table.
Message: OLE DB or ODBC error. .... The current operation was cancelled because another operation the the transaction failed.
If on the source I have a new column, the script generated by SqlPackage.exe recreates the table on the background with moving the data into a temp storage. If the table is big, such approach can cause issues.
Example of the script is below: in the source project I added columns [MyColumn_LINE_1]  and [MyColumn_LINE_5].
Is there any way I can make it generating an alter statement instead?
BEGIN TRANSACTION; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; SET XACT_ABORT ON; CREATE TABLE [dbo].[tmp_ms_xx_MyTable] ( [MyColumn_TYPE_CODE] CHAR (3) NOT NULL,
[Code] ....
The same script is generated regardless the table having data or not, having a clustered or nonclustered PK.
The SP UserPersist_GetByCriteria does a "SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture", so why am I receiving this error when both tables have the same structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
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 want to insert the data from temp table to other table. Only condition is, it needs to sorted based on tool number and tool date. For example if we have ten records for tool number 1000, it should be order by tool number and then based on tool_dt. Both tables doesn't have any primary keys. Please find below my code. I removed all the unnecessary columns for simple understanding. INSERT INTO tool_summary  (tool_nbr, tool_dt) select tool_nbr, tool_dt from #tool order by tool_nbr, tool_dt...But this query is not working as expected. Data is getting shuffled.
WE have a job that loads data from an Oralce DB into our SQL Server 2000 DB twice a day. The schedule has just changed so that now there is a possibility of having my west coast users impacted when it runs at 5 PM PST and my east coast users impacted when it runs at 7 AM EST. As a workaround, I have developed a DTS package that loads the data into temp tables instead of the real tables. IE. Oracle -> XTable_temp instead of Oracle -> XTable. The load sometimes takes about an hour to an hour and a half to load, so this solution works great, but I want to then lock the table, delete it and rename the temp table to table X. The pseudo code would be:
Begin Transaction
Lock Table XTable
Drop XTable
Alter Table XTable_temp rename to XTable
Release Lock XTable
End Transaction
Create XTable_temp
I see two issues with this solution. 1) I think if I can lock XTable that the lock would be released when the table is dropped and the XTable_temp was being renamed. 2) I can't find a command to rename a table.
I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare).
Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?
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’
I need to decide what is better to use: global temp table ( I can't use local one) or permanent table in SQL 2000 stored procedures. I extract data from linked server table and update several tables on our server. Those procedures scheduled to run every 3 hours.
Another question: for some reasons when I used global temp table, I wasn't able to schedule multi steps with every step executing one of the stored procedures.I think global temp tables should be visible to other stored procedures, right?
