Slow query vs. comments in the code

Question

Slow query vs. comments in the code

everand76

Old Hand

Points: 338
More actions
October 24, 2019 at 11:27 am

#3691552
I have query like this:
1.select into table2 from table1
2.select function(table2.field1) from table2
3.lot of commented code
Whole query runs about 25s. Running it separately - (1) and next (2) it runs (altogether) about 3s. After removing comments (3) whole query (1+2) runs 3s too. Adding same comments again - 25s. The function from (2) converts UTC datetime to local time and operates on its own variables and calculations only (no queries to any tables, views, ect.). When i remove this function (select table2.field1 without calculations) it runs 3s - no mather if comments are present or not
Additional info:
I have replaced the function (another one) - this same hapens. I have noticed that query runs as long as size of comments. I have added comments to 700 rows - almost 50s, 350 rows of comments - 26s, 170 rows - 14s. Comments placed in the top or at the bottom - no matter. It looks like all comments was processed by function some way... What on earth!?
I can say, that this issue is related to one of two servers only. Very strange. Any idea?
- This topic was modified 5 years, 10 months ago by everand76.

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 1

To be clear, you don't mean comments in the actual code itself... you mean comments in a "comment" column in the table itself, yes?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

everand76 Old Hand Points: 338 More actions · Answer 2

I don't catch your question (my english) but I mean statements like this:

/*1*/

select t1.DateUTC into #Tmp1 from Table1 t1

/*2*/

select function(t2.DateUTC) DateLocal from #Tmp1 t2

/*3*/

/*

select

t3.Field1

,t3.Field2

,t3.Field4

...

from TableX t3

join TableY t4 on ...

......

*/

If I delete section /*3*/ - "comments", the whole query (understood as all three secions) works fast. With this commented code - the more records within section /*3*/ the slower execution

Mr. Brian Gale SSC-Insane Points: 24936 More actions · Answer 3

That is very strange. Can you post the execution plan? If not, I'd start by examining that.

My understanding is that comments SHOULD be stripped out when it gets to the query optimizer.

My GUESS is that somewhere in section 3 your comment block is ending and SOME code is being run.

In the coding standards we have at work, commented out SQL code should not hit a production box. While you are testing, it is OK to leave it in there, but once we get to a production box we try to have no commented out code. Comments still exist in code, but no code exists in the comments. This isn't for performance purposes, but for code readability and maintenance.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

everand76 Old Hand Points: 338 More actions · Answer 4

I know - I sound like I'm crazy, but it's real life. Execution plans are identical- with comments and without. You have right - comments should be ignored, but on production server are not (other server's behavior is proper - comments are ignored in 100%). For sure there is no uncomented code in section 3 - I replace this comments by only "-" signs and nothing changed.

I understand necesiitiy (good practice) to delete old / useless code, but in my opinion there is something wrong with my PROD server - it should not be happen

Mr. Brian Gale SSC-Insane Points: 24936 More actions · Answer 5

In the execution plan, are you getting any extra memory grants or spills to tempdb or anything? I expect that something is different somewhere.

And I believe you; I've seen weird things that don't make sense initially before, but after digging a bit it makes sense... eventually.

In your case, it sounds like some weird bug with something... what about if you turn STATISTICS IO ON and STATISTICS TIME ON? Might be interesting to see if there is a lot of CPU time or a whole bunch of crazy reads happening for some reason.

Is this part of a stored procedure or is it straight up TSQL that you are running? I am wondering if maybe it is something on the TSQL side NOT on the SQL Server side?

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

everand76 Old Hand Points: 338 More actions · Answer 6

At this moment I can say, that statistics time shows huge CPU time processing in section 2 - more comments - biger that time. It looks like comented code is somehow processed via function (remeber - if I remove function from section 2 the problem is gone). I will back with execution plans, because I'm not sure all aspects

PS. It is straight TSQL code run (not a stored procedure). This same (100% 1:1) code is run on second server (reporting server with transactional replication). Function definitions are same too but as I'have wrote using another function doesn't change anything.

Jeff Moden SSC Guru Points: 1004645 More actions · Answer 7

everand76 wrote:

I know - I sound like I'm crazy, but it's real life. Execution plans are identical- with comments and without. You have right - comments should be ignored, but on production server are not (other server's behavior is proper - comments are ignored in 100%). For sure there is no uncomented code in section 3 - I replace this comments by only "-" signs and nothing changed.
I understand necesiitiy (good practice) to delete old / useless code, but in my opinion there is something wrong with my PROD server - it should not be happen

I'm with BMG002... As crazy as it sounds, I absolutely do believe this can happen because I've actually seen stuff like this before both in code and comments in code. For me, it turned out to be a special but hidden character in the code. It wouldn't copy to another machine so it looked like there was something wrong with the machine it was on.

The way I found the odd invisible character was to split the code (from sys.sql_modules) and check each character's ASCII() value for values outside the normal range. I forget what the character was (it only appeared once in my case... sounds like one might appear more than once n your case) but, once I got rid of it by deleting and retyping that particular line of code, things started working correctly.

I still have no idea how the bad character got into the code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

everand76 Old Hand Points: 338 More actions · Answer 8

My steps:

Created table (TestTable) on Server2 (this "good" one) with 1000 rows: Id (bigint), DateTimeUTC (datetime) populated with data
Copied this table to Server1 (same data on both)
No index on that table
Opened two separate SSMS aplications connected to each servers
Opened new windows to enter TSQL code
Created code from the beginning (no Ctrl+C / Ctrl + V) on both applications
Run it

Query1 - same on both servers (execution time is miliseconds)

Query2 - big different coused by commented lines (100 rows of comments)

Any idea?

PS.

Comments looks like:

/*

-----------

(...)

----------

*/

This reply was modified 5 years, 10 months ago by everand76.

Attachments:

You must be logged in to view attached files.