Transact SQL :: Create Index On Temp Table To Reduce Run Time Of Update Query
Apr 29, 2015
I want to create index for hash table (#TEMPJOIN2) to reduce the update query run time. But I am getting "Warning!
The maximum key length is 900 bytes. The index 'R5IDX_TMP' has maximum length of 1013 bytes. For some combination of large values, the insert/update operation will fail". What is the right way to create index on temporary table.
Update query is running(without index) for 6 hours 30 minutes. My aim to reduce the run time by creating index.
And also I am not sure, whether creating index in more columns will create issue or not.
Attached the update query and index query.
CREATE NONCLUSTERED INDEX [R5IDX_TMP] ON #TEMPJOIN2
(
[PART] ASC,
[ORG] ASC,
[SPLRNAME] ASC,
[REPITEM] ASC,
[RFQ] ASC,
I pulled some examples of using a subquery pivot to build a temp table, but cannot get it to work.
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL DROP TABLE #Pyr GO SELECT vst_int_id, [4981] AS Primary_Ins, [4978] AS Secondary_Ins,
[code]....
The problems I am having are with the integer data being used to create temp table fields. The bracketed numbers on line 7-10 give me an invalid column name error each. In the 'FOR', I get another error "Incorrect syntax near 'FOR'. Expecting '(', or '.'.". The first integer in the "IN" gives me an "Incorrect syntax near '[4981]'. Expecting '(' or SELECT". I will post the definitions from another effort below.
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
I have an Access app. that I am migrating the DB portion (queries, tables) to SQL server. I need to create a temp table that lasts as long as the user has the Access FE app. open. Idea is that the temp table stores the user's parameters (used for filtering data entry forms and report). The parameters allow the app. to only show the user his data (cannot view other users data). The SP shown below works OK, it creates a ##Temp table and updates it with the parameters sent by Access FE app. The issue I am having is that as soon as the SP finishes the ##Temp table is removed. I thought of using a regular table, but, I am currently testing this migration in my local SQL server instance, as soon as I move the database to production environment, then users will not be able to create tables as permissions are only read/write.
USE [Work_Allocation] GO /****** Object: StoredProcedure [dbo].[spUser_Parameters_update] Script Date: 9/30/2015 12:27:42 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[spUser_Parameters_update]
Hi,I have a task at hand to reduce the time taken for search query toexecute. The query fetches records which will have to sorted bydegrees away from the logged in user. I have a function whichcalculates the degrees, but using this in the search query slows theexecution and takes about 10 secs to complete which is unacceptable.Please advice. Your help is much appreciatedFor more details plz see:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=97021ThanksIsfaar
convert my table(like picture) to hierarchical structure in SQL. actually i want to make a table from my data in SQL for a TreeList control datasource in VB.net application directly.
ProjectID is 1st Parent Type_1 is 2nd Parent Type_2 is 3rd Parent Type_3 is 4ed Parent
I need to take a temporary table that has various times stored in a text field (4:30 pm, 11:00 am, 5:30 pm, etc.), convert it to miltary time then cast it as an integer with an update statement kind of like:
Update myTable set MovieTime = REPLACE(CONVERT(CHAR(5),GETDATE(),108), ':', '')
how this can be done while my temp table is in session?
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE] @QuePoolID int=null ,@ApptTime time(7) ,@SendOnDate datetime
I have a proc which creates a rather large temp table, and then i create an index on this. the problem arises when multiple users call this proc at that same time. the second user gets errors as they cannot create the index because it already exists. i know i can't just name the index #index_name, althought this would be ideal. does anyone know of a way to let multiple users create an index besides using dynamic sql? thanks in advance
I am trying to create a temp table with a non-clustered index.
Originally I tried to create the index after I created the table.
This seemed to work fine, so I added my stored procedure to our Production environment.
However, when two users called the stored procedure at once I got the following error:
There is already an object named 'IX_tmpTableName' in the database. Could not create constraint. See previous errors.
I then found that SQL Server does generate unique names for the temp table but not all the objects associated with the temp table if they are explicitly named.
This is easy enough to solve for a PRIMAY KEY or UNIQUE constraint because the do not have to be named.
Is there a way to create an non-clustered index on a temp table without naming it?
In a Stored Proc I am creating the following temp table and index:
CREATE TABLE [dbo].[#ShipTo]( [Ship_to_Num] [int] NOT NULL, [Country_key] [nvarchar](3) NULL, CONSTRAINT [PK_ShipTo] PRIMARY KEY CLUSTERED ( [ship_to_Num] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
The stored Proc runs fine from "exec", but when you batch into a Job it gives the error that "PK_ShipTo" already exists! I even put in a drop table on #ShipTo, but the same effect.
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.
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
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
Hi all!I have a problem with a temp table.I start creating my table:bdsqlado.execute ("CREATE TABLE #MyTable ...")There is no error. The sql string has been tested and when it'sexecuted in the sql query analyzer it really creates the table.After creating the table, I execute an insert statement:bdsqlado.execute ("INSERT INTO #MyTable VALUES(...) "It returns an error like this: "Invalid Object Name #MyTable"I don't understand what's wrong. If I execute both sql sentences inthe SQL Query Analyzer it works perfectly.I use the same connection to execute both statements and I don't closeit before the INSERT is executed.I think it may be something related to dynamic properties of theconnection, but I'm not sure. It's just an idea.Please I need help.Thanks a lot,
What is the best way to drop a temp table if it exists? I am looking something similar to this: if exists (select top 1 * from #TableName) then drop #TableName else end
Can someone send me an example of creating a variable to use instead of a temp table? I cannot find an example on books on line, but know it is possible in SQL2000.
I need to create a dynamic temporary table in a SP. Basically, I am using the temp table to mimic a crosstab query result. So, in my SP, I have this:--------------------------------------------------------------------------------------- Get all SubquestionIDs for this concept-------------------------------------------------------------------------------------DECLARE curStudySubquestions CURSOR LOCAL STATIC READ_ONLY FOR SELECT QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesFROM RotationMaster AS RM INNER JOIN RotationDetailMaster AS RDM ON RM.Rotation = RDM.Rotation INNER JOIN QuestionGroupMaster AS QGM ON RDM.QuestionGroupNumber = QGM.QuestionGroupNumber INNER JOIN QuestionGroupDetailMaster AS QGDM ON QGM.QuestionGroupNumber = QGDM.QuestionGroupNumberWHERE RM.Study = @StudyGROUP BY QGDM.SubquestionID, QGDM.ShortName, QGDM.PosRespValuesHAVING QGDM.SubquestionID <> 0--------------------------------------------------------------------------------------- Dynamically create a Temp Table to store the data, simulating a pivot table-------------------------------------------------------------------------------------SET @Count = 2SET @SQL = 'CREATE TABLE #AllSubquestions (Col1 VARCHAR(100)'OPEN curStudySubquestionsFETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValuesWHILE @@FETCH_STATUS = 0BEGIN SET @SQL = @SQL + ', Col' + CAST(@Count AS VARCHAR(5)) + ' VARCHAR(10)' SET @Count = @Count + 1 FETCH NEXT FROM curStudySubquestions INTO @SubquestionID, @ShortName, @PosRespValues ENDSET @SQL = @SQL + ', ShowOrder SMALLINT)'CLOSE curStudySubquestionsPRINT 'Create Table SQL:'PRINT @SQLEXEC (@SQL)SET @ErrNum = @@ERROR IF (@ErrNum <> 0) BEGIN PRINT 'ERROR!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!' RETURN ENDPRINT '*** Table Created ***'-- Test that the table was createdSELECT *, 'TEST' AS AnyField FROM #AllSubquestions The line PRINT @SQL produces this output in Query Analyzer (I added the line breaks for forum formatting):CREATE TABLE #AllSubquestions (Col1 VARCHAR(100), Col2 VARCHAR(10), Col3 VARCHAR(10), Col4 VARCHAR(10), Col5 VARCHAR(10), Col6 VARCHAR(10), Col7 VARCHAR(10), ShowOrder SMALLINT) However, the SELECT statement to test the creation of the table produces this error:*** Table Created ***Server: Msg 208, Level 16, State 1, Procedure sp_SLIDE_CONCEPT_AllSubquestions, Line 73Invalid object name '#AllSubquestions'. It appears that the statement to create the table works, but once I try to access it, it doesn't recognize its existance. Any ideas?
I have a view which works fine but I cannot display the data in the Report tool because its
CCSID is HEX. If I could create it to temp table
I think then there would be an easy way to get around this problem.
This is the code:
CREATE VIEW astlib.acbalmpk AS ( (SELECT LMLTPC, COALESCE(IRLOC1,'') as IRLOC1, COALESCE(IRLOC2,'') as IRLOC2, COALESCE(IRLOC3,'') as IRLOC3, IRPRT#, IRQOH#, IRWHS#, '' as IEPRT#, '.00' as IEQOH#, '' as IELOC1, '' as IELOC2, '' as IELOC3, '' as IERIDC, '' as IEWHS#
I'm trying to update a table from a temporary table but I'm struggling a little with the syntax. Here's what I have so far:
DECLARE @WeekNo INT SET @WeekNo = 1
WHILE @WeekNo <= 53 BEGIN SET @SQL = 'UPDATE [Targets] SET [Target] = SELECT ISNULL(Week' + Cast(@WeekNo as VarChar(2)) + ', 0) FROM #targets WHERE [CampaignID] = @CampaignID' SET @WeekNo = @WeekNo + 1 EXECUTE (@SQL) END
My temp table gets created OK, it's just setting the 'Target' field to the value of the same field in my temp table.
I get the following errors: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'SELECT'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near ' + Cast(@WeekNo as VarChar(2)) + '.
below is the complete code of two temp talbe , the first temp talbe i created so for others it is easy to see what fields I am copying into the temptable.
So what I am doing is that, copying the values from #RealTable into the #Rpt table. Later I want to check the values into the some column and than on certain condtions I am going to update the values. But the way I am doing is I am using the update statment to change the values, where as I think there must be a better way of doing what I am doing in these codes. So instead of using the updates like 6 times or more I can just use them maybe twice. Or probably not at all. Please give your idea. Thanks. Code is running and you can try. I just depict a litttle code here but in real i have 30 columns and the data is coming from the complex join statments. And I am doin the updates for 8 times.
) Insert INTO #Rpt Select LastName ,Phone , MissingInfo ,ErrMessage ,'' AS TextMessage ,'' AS CheckMark From #RealTable
Select * from #Rpt
--Now i saw the result which is what I have inserted. --but I want to fill the values of the TextMessage and the CheckMark. --So there could be a way to do what I am doing following.
Update #Rpt Set TextMessage='Please send the signed copy' Where MissingInfo='Need More Paper'
Update #Rpt Set TextMessage='Send us photo copy of your ID' Where MissingInfo='Need ID'
Update #Rpt Set TextMessage='Send the legal copy of the document' Where MissingInfo='Legal Documentation'
Update #Rpt set CheckMark='a' where MissingInfo in ('1','3','4') AND ErrMessage='Need More Paper'
Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation. I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices. Basically, a shopping cart! I thought of using session variables and string manipulations but I am more comfortable with DB queries. a simple 4 column table would cover everything. SQL server and VBScript Thanks M
