March 15, 2024 at 5:27 am
I am getting an error
Msg 468, Level 16, State 9, Procedure dbo.WIP_Query_V2, Line 462 [Batch Start Line 2]
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.Line 462 is a comment not code so not sure where the issue really is.
The only spot I can think of where this could possibly come from is the create table b/c that is the only new code I added (plus the clustered index):
CREATE TABLE #NearTermData(
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
[EXPIRATION] [date] NOT NULL,
[ROOT_SYMBOL] [nvarchar](10) NOT NULL,[int] NOT NULL,
[CALL_BID] [numeric](28, 22) NOT NULL,
[CALL_ASK] [numeric](28, 22) NOT NULL,
[PUT_BID] [numeric](28, 22) NOT NULL,
[PUT_ASK] [numeric](28, 22) NOT NULL,
[PRICE_DIFFERENCE] [numeric](28, 22) NOT NULL
) ON [PRIMARY]What did I do wrong?
It looks like the collation of tempb is different from the database you have the tables. You should really make sure tempdb has the same collation as the rest of the databases on the instance.
You can get around it by specifying the collation of the text columns on the temporary table when you create it.
March 15, 2024 at 5:07 pm
One more thing...
The create table and create index code was created before any rows were added to the temp tables. Not sure why the run time grew like that. Any suggestions to help investigate is much appreciated.
March 15, 2024 at 6:50 pm
One more thing...
The create table and create index code was created before any rows were added to the temp tables. Not sure why the run time grew like that. Any suggestions to help investigate is much appreciated.
Inserting rows into a table with a clustered index is slower than into a table with no index because the rows need to be carefully placed upon insertion. If the query following the creation and filling of a temporary table doesn't improve in speed more than the additional time taken to insert rows into a table with a clustered index, the entire query will take longer. Enhancing performance involves figuring out what will make the entire query run the fastest. This is typically achieved through a combination of experienced guesswork and timed experiments using various methods.
The most efficient approach might involve consolidating the entire process into a single query. Alternatively, it could be better to insert rows into a temporary table and immediately utilise it for the next stage of the query. Another option might be creating a non-clustered index on the table after inserting the rows, or establishing a temporary table with a clustered index before insertion. Improving performance is an iterative process that requires experimentation and adjustment.
If you can, run the query in SSMS with SET STATISTICS IO, TIME ON; as the first line to see how long each part takes and how much it uses the system. Also, check how it's using the indexes in the execution plan. Once you've made the temporary table, try changing the following query without making the temporary table again each time.
March 15, 2024 at 8:39 pm
Collation for #Temp table is the same as default collation for the server. Table definition
CREATE TABLE #NearTermData(
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
[EXPIRATION] [date] NOT NULL,
[ROOT_SYMBOL] [nvarchar](10) NOT NULL,
[int] NOT NULL,
[CALL_BID] [numeric](28, 22) NOT NULL,
[CALL_ASK] [numeric](28, 22) NOT NULL,
[PUT_BID] [numeric](28, 22) NOT NULL,
[PUT_ASK] [numeric](28, 22) NOT NULL,
[PRICE_DIFFERENCE] [numeric](28, 22) NOT NULL
) ON [PRIMARY]
looks identical to this one:
CREATE TABLE RealTable_NearTermData(
[UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL,
[QUOTE_DATE] [date] NOT NULL,
[EXPIRATION] [date] NOT NULL,
[ROOT_SYMBOL] [nvarchar](10) NOT NULL,
[int] NOT NULL,
[CALL_BID] [numeric](28, 22) NOT NULL,
[CALL_ASK] [numeric](28, 22) NOT NULL,
[PUT_BID] [numeric](28, 22) NOT NULL,
[PUT_ASK] [numeric](28, 22) NOT NULL,
[PRICE_DIFFERENCE] [numeric](28, 22) NOT NULL
) ON [PRIMARY]
It just looks like, but temp and original table do not6 have same definition. You have two character columns, [UNDERLYING_SYMBOL] [nvarchar](10) NOT NULL, and, [ROOT_SYMBOL] [nvarchar](10) NOT NULL,.
Provided definitions are not complete. There is parameter COLLATION which decides how the rows will be sorted, which is critical for index definition. Index firstly dictates sort for columns and then maybe uniqueness.
You can find collations (and other useful info) in system view [INFORMATION_SCHEMA].[COLUMNS];
USE YourDatabase;
SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS];
Good link about collations: https://www.mssqltips.com/sqlservertip/6386/sql-server-collation-overview-and-examples/
Zidar's Theorem: The best code is no code at all...
March 16, 2024 at 4:00 pm
I am surprised that no one else answered the original question - and this just evolved into using temp tables and trying to solve the performance issue using a temp table.
The correct answer to the original question is: It Depends
You cannot say that using a temp table will improve performance - nor can you say not using a temp table will improve performance, since it all depends on that specific query, the indexes available from all of the tables involved - and the execution plan that is generated.
Some times - using derived tables or common table expressions works better because SQL Server can optimize the query to process the data efficiently. Some times - moving a derived table into a temp table (with or without indexing) can help the overall processing by reducing the number of rows and IO being processed (also known as divide & conquer).
And sometimes - using a completely different approach will be the solution.
I have taken queries using multiple temp tables - converting those to CTEs, iTVFs and CROSS/OUTER APPLY and improved performance from hours to seconds. I have also taken queries using derived tables and/or CTEs and broken them out into temp tables and improved performance by many factors.
So - to say it again: It Depends
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 16, 2024 at 4:10 pm
I am surprised that no one else answered the original question - and this just evolved into using temp tables and trying to solve the performance issue using a temp table.
The correct answer to the original question is: It Depends
You cannot say that using a temp table will improve performance - nor can you say not using a temp table will improve performance, since it all depends on that specific query, the indexes available from all of the tables involved - and the execution plan that is generated.
Some times - using derived tables or common table expressions works better because SQL Server can optimize the query to process the data efficiently. Some times - moving a derived table into a temp table (with or without indexing) can help the overall processing by reducing the number of rows and IO being processed (also known as divide & conquer).
And sometimes - using a completely different approach will be the solution.
I have taken queries using multiple temp tables - converting those to CTEs, iTVFs and CROSS/OUTER APPLY and improved performance from hours to seconds. I have also taken queries using derived tables and/or CTEs and broken them out into temp tables and improved performance by many factors.
So - to say it again: It Depends
Yes, we really need to see the actual query to make better judgements on what needs to be done.
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply