Large scripts still slow

PDinCA

3.5 Production version is still slow handling longish SPs. The one I wanted to change just three consecutive lines within is only 700 lines in total, with 600 between the AS and GO. The cursor takes 5 to 10 seconds to register at the clicked location, then each cursor movement takes another 5 seconds to register. I made my changes, went to another window, and to Outlook, then came back to add SET NOCOUNT ON at the top of the code, and that took ages, too.

Ideas?

Bart Read

Hi Stephen,


This sounds like a configuration issue. Open the Options dialog by clicking on SQL Prompt > Options from the main menu bar in your editor. Now switch to the "Listed Candidates" tab and select the "Variables and Parameters" screen (bottom link on left-hand side).

If you could tell me how you've got this set up that might give me a clue as to what the problem is, however in general if you're experiencing performance problems I'd recommend selecting "Search a fixed number of lines from the caret (faster)" rather than "Search entire batch/GO block (slower, but more accurate)". There's also a "Tips" section in the web help (available via SQL Prompt > Help on the main menu again) that briefly deals with performance issues.


Thanks,
Bart

PDinCA

I read the tips for 3.x and 3.5 both in the Forum and Help and because all variables are declared in the SP parameter list or at the beginning of the code, the "fixed lines from caret" doesn't help. Many of our SP's use "whole SP" scope variables, where they are declared, set once, then used throughout the code. An example is

DECLARE @Now datetime
SET @Now = CURRENT_TIMESTAMP

which enables us to assign a common timestamp to all records modified within the unit of work, and gives a consistent value for all comparisons agains datetime columns to see if, for example, a Product is now "Available", as GETDATE() can give a different value even if used twice in the same SELECT, as I found yesterday when an obscure bug manifested involving

CASE
   WHEN GETDATE() >= ISNULL(Product.AvailableDate,GETDATE()) THEN...
   ELSE ...
END

dropped to the ELSE when the AvailableDate was NULL.

Your advice is well taken, but in the cases presented to me, I would have to go through scores of SPs to resituate DECLAREs closest to the first point of use, then run the risk of later changes needing to use the variable before it is declared, just to accommodate SQL Prompt's sluggishness in handling larger blocks of code. My hope is that the SQL Prompt gurus will be able to further optimize the code, and/or find sets of conditions whereby entire blocks don't need to be scanned so frequently, especially when all one is doing is placing the cursor at a character location, then holding Shift while I move the cursor down and over to select three lines then Delete...

Bart Read

Once it's found the variables it does remember them you know. It doesn't forget them as soon as they're outside of the 50 line limit. And in any case when you initially open the file you should find it picks up anything defined near the top automatically. You can also always increase the number of variables that it remembers if a few are dropping out.

PDinCA

Thanks, that wasn't clear from the initial reading...

It doesn't quite explain why just positioning and highlighting a few lines takes 20 seconds or more to complete... I guess that's one for boys...

Bart Read

The reason is that SQL Prompt updates its completion lists in response (mainly) to the caret being moved, rather than because an edit has occured. There's definitely more room for tuning this, but as I say, if you use the options I suggest it should cure the problems you've been seeing. The one exception is if you indent a really large block of text because that just looks like one humungous edit to SQL Prompt at the moment. It's also a bit of a trade-off, so in your case, with your client machine spec I'd also suggest you might want to play around with increasing the number of lines scanned. I suspect you could go up to 100 or 150, maybe more, before you started noticing any negative impact on performance.


Thanks,
Bart

PDinCA

I set the lines to 75 with 100 variables (overkill on the latter perhaps, but I'll see how it goes...)

Yes, the indent a large block is a coffee-break activity... If I remember, I turn Prompt off then indent, then back on. If not, it's time for a wet...

Thanks for the comprehensive answers and clarifications. Maybe I'll take a turn around the help with so many changes being made for 3.5.

Bart Read

No problem. It's definitely worth just tinkering with the options a bit as well to see what works best for you, because it's not as though you can ever do any real harm by setting something wrong, and if the worst comes to the worst just hit the "Reset All Defaults" button.
Cheers,
Bart

jkz999

I have upgraded to version 3.5 but I still see CPU spiking up to 98% (isqlw.exe process) when I load an insert script. The script is too wide to post here, though I can if you like, but it is a sraight
INSERT INTO [table_name] (column list) values(1,1,...)

It contains 11 Insert statements into a table with 65 columns.

This is enough to make me turn it off.

Dave J

Bart Read

Hi Dave,


Are you sure that's not just because it's caching your database for the first time, which if it's large is likely to take some time. Is the toast popup visible in the bottom right hand corner (assuming you didn't close it)? And if you leave it for a few minutes does the CPU usage drop back to normal? I'd also recommend turning off automatic cach updates (uncheck the box in the Cache Management dialog available via SQL Prompt > Cache Management).


Thanks,
Bart

jkz999

Hello Bart

I'm afraid it's definitely not caching. I can open a connection to the db, watch the window appear , then disappear, then load the script and then watch my CPU spike up to 99% for up to 3 minutes. When I get control back, if I click anywhere else in the window, it appears to re-parse and takes an age to come back again. Note, this is just clicking elsewhere in the window, not typing anything.

I can post the table DDL and the DML script if you think it will help you solve this

Dave J

Bart Read

Hi Dave,


Thanks for getting back to me. Can you open the Options dialog, and then to the "Variable and Parameters" screen on the "Listed Candidates" tab, and then make sure that "Search a fixed number of lines..." is selected please? You might want to try playing with the settings on this page for best results. If that doesn't sort the problem out please let me know.

BTW: there's also some extra info on performance in the Tips and Troubleshooting sections in the online help.


Thanks,
Bart

jkz999

Bart Read wrote:

Can you open the Options dialog, and then to the "Variable and Parameters" screen on the "Listed Candidates" tab, and then make sure that "Search a fixed number of lines..." is selected please?

I did, and it is selected and is set to 50 lines. The script in question contains no parameters or variables btw, it's a straight insert into an admittedly very wide table.

HTH

Dave

Bart Read

OK thanks. Could you do me a favour and post the table creation script and the insert script please? Just a single INSERT statement should be fine.
Thanks,
Bart

jkz999

/*
Here you go. Currently, DA_Avery_Txn_Temp contains 2,501,247 rows.

Note, this script creates the table on a file group called SECONDARY.

*/

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DA_Avery_Txn_Temp]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [DA_Avery_Txn_Temp] (
[Avery_Txn_Id] [int] IDENTITY (1, 1) NOT NULL ,
[Registration] [varchar] (15) COLLATE Latin1_General_CI_AI NULL ,
[Station_No] [varchar] (20) COLLATE Latin1_General_CI_AI NULL ,
[Txn_Dt] [datetime] NULL ,
[Txn_No] [int] NULL ,
[Ticket_No] [int] NULL ,
[Weighing_Mode] [int] NULL ,
[Weighing_Type] [char] (2) COLLATE Latin1_General_CI_AI NULL ,
[Charge_Type] [char] (1) COLLATE Latin1_General_CI_AI NULL ,
[Customer] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Haulier] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Waste] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Destination] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Driver] [varchar] (15) COLLATE Latin1_General_CI_AI NULL ,
[UDF1] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[UDF2] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[UDF3] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[UDF4] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[UDF5] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[UDF6] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[UDF7] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[UDF8] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[First_Weight] [decimal](9, 3) NULL ,
[First_Weight_Unit] [char] (1) COLLATE Latin1_General_CI_AI NULL ,
[First_Weight_Dt] [datetime] NULL ,
[First_Weight_Consec] [decimal](9, 3) NULL ,
[Second_Weight] [decimal](9, 3) NULL ,
[Second_Weight_Unit] [char] (1) COLLATE Latin1_General_CI_AI NULL ,
[Second_Weight_Dt] [datetime] NULL ,
[Second_Weight_Consec] [decimal](9, 3) NULL ,
[Net_Weight] [decimal](11, 3) NULL ,
[Adjusted_Net_Weight] [decimal](11, 3) NULL ,
[Charge_Rate] [money] NULL ,
[Goods_Charge] [money] NULL ,
[VAT] [money] NULL ,
[Price] [money] NULL ,
[Manual_Customer] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Manual_Haulier] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Ded_Reason_Name1] [varchar] (15) COLLATE Latin1_General_CI_AI NULL ,
[Ded_Reason_Name2] [varchar] (15) COLLATE Latin1_General_CI_AI NULL ,
[Ded_Reason_Name3] [varchar] (15) COLLATE Latin1_General_CI_AI NULL ,
[Ded_Reason_Comments1] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Ded_Reason_Comments2] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Ded_Reason_Comments3] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Ded_Reason_Rate1] [money] NULL ,
[Ded_Reason_Rate2] [money] NULL ,
[Ded_Reason_Rate3] [money] NULL ,
[Ded_Reason_Type1] [decimal](9, 3) NULL ,
[Ded_Reason_Type2] [decimal](9, 3) NULL ,
[Ded_Reason_Type3] [decimal](9, 3) NULL ,
[Ded_Total_Units] [decimal](9, 3) NULL ,
[Ded_Total_Weight] [decimal](9, 3) NULL ,
[Source] [varchar] (50) COLLATE Latin1_General_CI_AI NULL ,
[Transfer_Status] [char] (1) COLLATE Latin1_General_CI_AI NOT NULL CONSTRAINT [DV_DA_Avery_Txn_Temp_Transfer_Status] DEFAULT ('N'),
[Error_Desc] [varchar] (255) COLLATE Latin1_General_CI_AI NULL ,
[Created_By] [varchar] (12) COLLATE Latin1_General_CI_AI NOT NULL ,
[Created_Dt] [datetime] NOT NULL ,
[Modified_By] [varchar] (12) COLLATE Latin1_General_CI_AI NOT NULL ,
[Modified_Dt] [datetime] NOT NULL ,
[Ded_Reason_Count1] [decimal](9, 3) NULL ,
[Ded_Reason_Count2] [decimal](9, 3) NULL ,
[Ded_Reason_Count3] [decimal](9, 3) NULL ,
[Ded_Reason_Total1] [money] NULL ,
[Ded_Reason_Total2] [money] NULL ,
[Ded_Reason_Total3] [money] NULL ,
CONSTRAINT [XPKDA_Avery_Txn_Temp] PRIMARY KEY CLUSTERED
(
[Avery_Txn_Id]
) WITH FILLFACTOR = 90 ON [SECONDARY] ,
CONSTRAINT [DA_Avery_Txn_Temp_Transfer_Status] CHECK ([Transfer_Status] = 'N' or ([Transfer_Status] = 'T' or [Transfer_Status] = 'E' or [Transfer_Status] = 'D' or [Transfer_Status] = 'X'))
) ON [SECONDARY]
END

GO

SET NOCOUNT ON


PRINT 'Inserting values into [DA_Avery_Txn_Temp]'

SET NOCOUNT ON

PRINT 'Inserting values into [DA_Avery_Txn_Temp]'

INSERT INTO [DA_Avery_Txn_Temp]
([Registration],
[Station_No],
[Txn_Dt],
[Txn_No],
[Ticket_No],
[Weighing_Mode],
[Weighing_Type],
[Charge_Type],
[Customer],
[Haulier],
[Waste],
[Destination],
[Driver],
[UDF1],
[UDF2],
[UDF3],
[UDF4],
[UDF5],
[UDF6],
[UDF7],
[UDF8],
[First_Weight],
[First_Weight_Unit],
[First_Weight_Dt],
[First_Weight_Consec],
[Second_Weight],
[Second_Weight_Unit],
[Second_Weight_Dt],
[Second_Weight_Consec],
[Net_Weight],
[Adjusted_Net_Weight],
[Charge_Rate],
[Goods_Charge],
[VAT],
[Price],
[Manual_Customer],
[Manual_Haulier],
[Ded_Reason_Name1],
[Ded_Reason_Name2],
[Ded_Reason_Name3],
[Ded_Reason_Comments1],
[Ded_Reason_Comments2],
[Ded_Reason_Comments3],
[Ded_Reason_Rate1],
[Ded_Reason_Rate2],
[Ded_Reason_Rate3],
[Ded_Reason_Type1],
[Ded_Reason_Type2],
[Ded_Reason_Type3],
[Ded_Total_Units],
[Ded_Total_Weight],
[Source],
[Transfer_Status],
[Error_Desc],
[Created_By],
[Created_Dt],
[Modified_By],
[Modified_Dt],
[Ded_Reason_Count1],
[Ded_Reason_Count2],
[Ded_Reason_Count3],
[Ded_Reason_Total1],
[Ded_Reason_Total2],
[Ded_Reason_Total3])

VALUES ('A111AAA',
'LIVAAAA',
'Aug 11 2007 1:54:21:000PM',
400431,
287406,
0,
'2',
'2',
'LLIV004',
'1',
'FF',
'FF',
'ACCOUNT',
'0',
'',
'',
'',
'',
NULL,
'',
'',
6.680,
'1',
'Aug 11 2007 1:38:55:000PM',
128721.000,
4.720,
'1',
'Aug 11 2007 1:54:20:000PM',
128723.000,
1960.000,
1960.000,
NULL,
0.0000,
0.0000,
0.0000,
'LIVERPOOL SALVAGE',
'OWN HAULIER',
'',
'',
'',
'',
'',
'',
0.0000,
0.0000,
0.0000,
1.000,
0.000,
0.000,
0.000,
0.000,
'FEED',
'N',
NULL,
'BORSHA',
'Aug 11 2007 8:28:34:810PM',
'BORSHA',
'Aug 11 2007 8:28:34:810PM',
0.000,
0.000,
0.000,
0.0000,
0.0000,
0.0000)

Bart Read

Grand, thanks. Assuming the INSERT statement is formatted as you've posted it I'm pretty sure I now know exactly what's going on.

Firstly, you've fallen victim to a particularly bone-headed piece of regular expression based code that appeared in SQL Prompt 3.5. It didn't cause a problem in previous versions, but as we increased the number of SQL statements supported it became more of a problem, and particularly when coupled with the limited block size parsing, which generally increases performance.

You should find that increasing the block size from 50 lines to 150 lines fixes the problem (it will then be able to parse your complete INSERT statement, whereas at the moment it'll always fail since the statement is 130 lines long due to the size of the table).

Now whilst this will almost certainly improve the situation it probably won't completely cure it. The next version of SQL Prompt, which should be released at some point during September, should fix that.

Hope that helps.


Thanks,
Bart

jkz999

Bart, I'm afraid not.

First of all, you asummed incorrectly, But hey, how could you know? I didn't tell you. The insert script is one line per insert, I formatted so it would look OK in the thread. I increased the number of lines as suggested anyway but no difference. The SECONDAY file group may be a factor, let me try it in a different DB, but put the table in the PRIMARY file group...

Hmm. Seemed to help with the query as posted, but when I loaded the 11 line (1 line to each insert) script, I see the same behaviour.

I do appreciate your efforts in responding to this btw, and your honesty
Dave

Bart Read

Thanks Dave, OK, that was worth a shot, albeit misguided. Now I might actually have to put some effort into figuring out what's going on. I still think you might be a victim of the boneheaded regex code, but I don't think that's the whole story.

I'll create a database using your table definition and then try to recreate the issue with an INSERT script based on the statement you sent.


Thanks,
Bart

Bart Read

Dave, one last thing. Could you try disabling cross-database support to see if that makes any difference please? You'll find it in the Options dialog under "Listed Candidates > Cross-Database Support". I want to be absolutely sure this is a separate issue to the one reported here:

http://www.red-gate.com/MessageBoard/vi ... php?t=5457

If disabling the cross-database support improves the performance then it's likely the same issue, if not then I'll need to think again (again).


Thanks,
Bart

Bart Read

Gents, if you're still having problems could you give this a whirl please?

http://www.red-gate.com/MessageBoard/vi ... php?t=5606

It's an early access release of SQL Prompt 3.6. I'm hopeful you should see a significant improvement. If not please let me know.


Thanks,
Bart