May 5, 2022 at 1:23 pm
First I can't stress how angry I am with Microsoft for removing debugging from SSMS. Second, I am using Visual Studio to debug now. When I build a cursor, the fetch command takes about 30 seconds to complete. Does anyone know why?
Thanks.
May 5, 2022 at 6:32 pm
First I can't stress how angry I am with Microsoft for removing debugging from SSMS. Second, I am using Visual Studio to debug now. When I build a cursor, the fetch command takes about 30 seconds to complete. Does anyone know why?
Thanks.
Without any code to look at, that's nearly impossible.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 5, 2022 at 8:44 pm
To add to what Michael said, if you run that in SSMS without the debugger, is it taking 30 seconds to grab data from the fetch?
Also, are you SURE you need a cursor?
I know I've been doing DBA and DB Development for several years now (more than 10) and I have yet to find a good use case for the debugger that can't be solved with a few extra debugging output steps put in (ie SELECT/PRINT statements). Debugging a stored procedure? Simply break it out into a TSQL statement in SSMS, and run it with a few extra SELECT/PRINT statements. Need a breakpoint? Simply run the code to that point by highlighting what you want to run. My opinion, TSQL shouldn't really need a debugger... There MAY be use cases for it, but I have not found a need for it. PLUS the debugger (from my understanding) will hold an open transaction while it is running including all locks. So it would be VERY easy to accidentally connect to live, hit a breakpoint, and pause a bunch of other live work from happening. Using the approach I suggested, as long as you don't have explicit transactions in your TSQL, your blocking should stop once you hit the last highlighted line.
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.
May 5, 2022 at 9:52 pm
I think the real problem here is that you have a cursor - and you need to debug that cursor to figure out why it is doing something you don't expect. That would be the only use case I can think of where the debugger might be utilized - and then only if you are performing more than just a couple of operations during each iteration.
With that said - is the cursor defined as a static forward only cursor - or is it defined as a dynamic cursor? If dynamic - that is going to be your problem because SQL has to maintain position in the cursor and allow you to move forward/backward, which is going to take a lot longer for the cursor to be set up as well as much longer as you move through the cursor.
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
May 6, 2022 at 11:40 am
@Mr. Gale - No only when debugging in VS does it take long. When I would debug in SSMS it would not do that. I like to step though my code before I consider it "production". It helps me process what is going on. I can understand your point about print statements but its just how I have been testing my code for 20+ years dating back to dBaseIV.
@jeff, it is a default cursor so I would assume it is forward only? I have use dynamic in the past and have specified that.
May 6, 2022 at 12:00 pm
@Jeff, it is a default cursor so I would assume it is forward only? I have use dynamic in the past and have specified that.
Can you show us your code?
The fact that you are assuming how a default cursor behaves indicates that you do not know how these things work. Your assumption is incorrect. And, your reliance on the debugger is also is an indicator of a lack of understanding about your code. As Brian said, I'm betting you do not even need a cursor. And, as Jeffery said, how did you declare the cursor?
Please, let us look at your code. You will learn some things.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 6, 2022 at 12:07 pm
OK, I love to learn new things. Thought I posted code yesterday but possibly
declare @partno char(35)
declare @rev char(3)
declare @id numeric(15)
declare @id2 numeric(15)
declare @std numeric(1)
declare @prc numeric(1)
--Get list of downrev items where the conditions are true
Declare stdprc cursor for select fpartno, frev, inmastx.identity_column, flstdpart, CHKPRICELIST from m2mdata01..inmastx
left outer join M2Mdata01..inmast_ext on fkey_id = inmastx.identity_column
where fpartno+frev not in (Select fcpartno+fcpartrev from m2mdata01..invcur where flanycur = 1)
and (flstdpart = 1 or CHKPRICELIST = 1)
open stdprc
Fetch next from stdprc into @partno, @rev, @id, @std, @prc
While @@FETCH_STATUS = 0
Begin
--Get the current rev part id for update later
set @id2 = (Select inmastx.identity_column from m2mdata01..inmastx
inner join m2mdata01..invcur on fpartno = fcpartno and frev = fcpartrev and flanycur = 1
where fcpartno = @partno)
--If down rev std component then change to current rev
If @std <> 0
Begin
Update m2mdata01..inmast_ext set flstdpart = 0 where fkey_id = @id
update m2mdata01..inmast_ext set flstdpart = 1 where fkey_id = @id2
End
--If down rev Price list then change to current rev
If @prc <> 0
Begin
Update m2mdata01..inmast_ext set CHKPRICELIST = 0 where fkey_id = @id
update m2mdata01..inmast_ext set CHKPRICELIST = 1 where fkey_id = @id2
End
Fetch next from stdprc into @partno, @rev, @id,@std, @prc
End
Close stdprc
Deallocate stdprc
forgot. I will post.
May 6, 2022 at 1:01 pm
OK, I love to learn new things. Thought I posted code yesterday but possibly
declare @partno char(35)
declare @rev char(3)
declare @id numeric(15)
declare @id2 numeric(15)
declare @std numeric(1)
declare @prc numeric(1)
--Get list of downrev items where the conditions are true
Declare stdprc cursor for select fpartno, frev, inmastx.identity_column, flstdpart, CHKPRICELIST from m2mdata01..inmastx
left outer join M2Mdata01..inmast_ext on fkey_id = inmastx.identity_column
where fpartno+frev not in (Select fcpartno+fcpartrev from m2mdata01..invcur where flanycur = 1)
and (flstdpart = 1 or CHKPRICELIST = 1)
open stdprc
Fetch next from stdprc into @partno, @rev, @id, @std, @prc
While @@FETCH_STATUS = 0
Begin
--Get the current rev part id for update later
set @id2 = (Select inmastx.identity_column from m2mdata01..inmastx
inner join m2mdata01..invcur on fpartno = fcpartno and frev = fcpartrev and flanycur = 1
where fcpartno = @partno)
--If down rev std component then change to current rev
If @std <> 0
Begin
Update m2mdata01..inmast_ext set flstdpart = 0 where fkey_id = @id
update m2mdata01..inmast_ext set flstdpart = 1 where fkey_id = @id2
End
--If down rev Price list then change to current rev
If @prc <> 0
Begin
Update m2mdata01..inmast_ext set CHKPRICELIST = 0 where fkey_id = @id
update m2mdata01..inmast_ext set CHKPRICELIST = 1 where fkey_id = @id2
End
Fetch next from stdprc into @partno, @rev, @id,@std, @prc
End
Close stdprc
Deallocate stdprcforgot. I will post.
Ok, I'm not going to be able to dig into this for a little while. That work thing is in the way this morning.
Here are some immediate questions for things you can look at:
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 6, 2022 at 1:52 pm
I guess you may be correct, an application programmer. That is what I have done for a very long time. For data manipulation, I guess, I would not know how to update the information without finding the information first of what needs to be updated. I need to find all down rev parts that have certain conditions then update them and then update the current rev parts that correlate to those down rev parts based on part number and rev. Please take your time but I DO WANT to understand so I may get better at it. I do appreciate all the time everyone puts in to helping us old app programmers out.
Table design: The part number is char(35) and the rev is char(3) in the table. The flstdpart, and chkprclist are bit. Should I change this?. The Identity_colums are Int, should I also change this?
The database was once Foxpro then they converted to SQL.
May 6, 2022 at 3:05 pm
First things - the cursor declaration is almost certainly a dynamic cursor since that is the default. It is a good practice to always specify how you want the cursor to work - for example: Declare cur Cursor Static Forward_Only For ...
A few other notes on your code:
Where (flstdpart = 1 Or CHKPRICELIST = 1)
And Exists (Select * From dbo.invcur Where flanycur = 1 And fcpartno = fpartno And fcpartrev = frev)
Now - looking at the above code, you can see how difficult it is to know what table each column comes from. If any of those columns are from the outer joined table then you have effectively turned that outer join into an inner join.
Finally - if you could provide some sample data with expected results (scripted as create and insert statements - temp tables and de-identified sample data) someone here could provide a working solution that can eliminate that cursor. My first instinct would be to use a cross/outer apply against the main query to return the 'id2' value for each row - and then it should be a simple matter to compare the values on each row and update accordingly with a CASE expression.
Note: it may be much simpler than that - but without sample data and expected results it really is just a guess.
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
May 6, 2022 at 3:29 pm
Jeff, you are correct. I had the cursor distinction mixed up. it is a dynamic cursor and I have used forward only in the past. The left outer joins are because there may not be an existing record in the joining table there fore it would leave out the selection in the first table. I don't want to miss any records. The databases are different. We have the production database in M2MData01 but the code is in M2MAux01. This was setup prior to me taking over for SQL Admin/Dev so I kept it as it. I do use Aliases from time to time such as "Join Database Alias on Alias.Fieldname just not in this instance. When you state "specifying the schema and moving away from 3-part naming" What do you mean by that? And Again, I appreciate you taking the time to teach me. I will look up SYNONYMS. I have used Exists so I understand that, Is it quicker to use that instead of what I did? I would assume so since you pointed it out and I will start to use that more often. I will also look up the cross/outer and Non-Sargable you stated.
The invcur table holds only current revisions of the items in the inmastx table. The inmast_ext table is a table that hold extra data for the inmastx table and is relational from fkey to identity_column of coarse you can see that I am just giving more info as to why I am doing what I am doing. I only need to use the invcur to get the lastest (current rev) of the part so that part can be update as well as the down rev part. The only thing that links the invcur to inmastx is partnumber and rev. INVcur will not have (or shouldn't) a downrev part but I have seen it in the past so thats is why test for it with flanycur. flanycur must be 1 but I have seen 0 in the table before. How would I upload test data?
May 6, 2022 at 4:13 pm
Let's start with 3-part naming - and schema-qualifying objects.
This structure dbname..table1 tells SQL Server to look for the object in the default schema and the dbo schema. If I created 2 tables as dbo.Table1 and myschema.Table1 in database dbname - then use that structure - if my default schema in that database is myschema then it would select the data from that schema. If my default schema is actually otherschema - then it would look to the dbo schema. There are several issues with this - first, SQL may not find the table - second, you can and will have multiple execution plans for each user/default schema which would be different.
And - if the schemas are not owned by the same principal you can end up with permission issues.
It is much better to always schema-qualify the objects.
3-part names: consider that you are referencing data in the database m2mdata1 - and the table invcur. Normally you would use the 3 part name of: m2mdata1.dbo.invcur (assuming dbo schema here). Now, lets say we have a new version of the database and instead of upgrading the existing we create a new database named m2mdata2. You now have to update all code that references m2mdata1.dbo.invcur to m2mdata2.dbo.invcur. Instead of doing that we can use a synonym:
CREATE SCHEMA m2mdata AUTHORIZATION dbo;
GO
DROP SYNONYM IF EXISTS m2mdata.invcur;
CREATE SYNONYM m2mdata.invcur FOR m2mdata1.dbo.invcur;
GO
And in our code we can now use: SELECT ... FROM m2mdata.invcur;
-- Update SYNONYM to point to new database
DROP SYNONYM IF EXISTS m2mdata.invcur;
CREATE SYNONYM m2mdata.invcur FOR m2mdata2.dbo.invcur;
GO
We changed the database name - and our existing code works and pulls the data from m2mdata2 now. No code changes - just updated the synonym.
As I stated before - get in the habit of *always* using an alias. And always using the alias for column references...this will help in the future because you won't have to think about which table has that column, especially if you are using derived tables or functions or CTE's.
Sample data:
Drop Table If Exists #invcur;
Create Table #invcur (col1 int, col2 int, ...);
Insert Into #invcur (col1, col2, ...)
Values (1, 2, ...)
, (2, 2, ...)
, (3, 3, ...);
If you can provide some data for each table - that includes enough data in each table - we can then copy/paste that into our system and write code against those temp tables. This way we can actually construct a working example that you can then use in your actual code. In some cases it will be as simple as replacing the temp tables with your real tables - in other cases it may be that you take the example and use the techniques shown in your actual code, but it allows for a working sample that can be run and shows the expected results.
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
May 6, 2022 at 4:18 pm
A little confused on the schema but will look up more reference on that. Understand on the creation of data. Everyone have a nice weekend. Nocking off for the day to take Mom to lunch, Will pick back up on Monday.
May 6, 2022 at 5:08 pm
This first update statement in your code:
UPDATE m2mdata01..inmast_ext
SET
flstdpart = 0
WHERE
fkey_id = @Id;
Assuming I GUESSED what columns belong to what tables and so forth, can replace the cursor, the rest of the logic and conditional statements, with this:
UPDATE M2IE
SET
M2IE.flstdpart = 0
FROM m2mdata01..inmast_ext M2IE
INNER JOIN m2mdata01..inmastx M2I ON M2IE.fkey_id = M2I.identity_column
WHERE
(M2IE.flstdpart = 1
OR M2IE.CHKPRICELIST = 1)
AND NOT EXISTS
(
SELECT
*
FROM
m2mdata01..invcur M2X
WHERE M2X.flanycur = 1
AND M2IE.fpartno = M2X.fcpartno
AND M2IE.frev = M2X.fcpartrev);
Now, without any kind of schema for each of the tables, that is a guess. And, I am sure that the rest of it can be simplified.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 6, 2022 at 5:19 pm
".... 2. Table aliases - you should get in the habit of aliasing your table references and using the alias on all column references. It is almost impossible for anyone else to be able to identify what table the columns are coming from which can lead to problems debugging the code...."
And make the aliases intuitive, not just 'A' , 'B' as I have seen some do. If the tables are OrderHeader and OrderDetail, alias as ORDH and ORDD or something similar.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply