VBA Rubberducking

TITLE: Microsoft SQL Server Management Studio
------------------------------

Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)

For help, click: go.microsoft.com/…

------------------------------
BUTTONS:

OK
------------------------------

mansellan

9:51 PM

uhm, that's worrying

this

you odn't have a open transaction, right?

Mathieu Guindon

nope

also this is the new TVF:

create function dbo.NormalizeSizes (@values SizedUnits)
returns @result table (
		 SourceRowId int not null primary key
		,SizeIndex int not null
		,Units int not null
		,unique(SourceRowId, SizeIndex)
)
begin

	with split as (
		select
			 v.SourceRowId
			,[1] = substring(v.Units,1,4)
			,[2] = substring(v.Units,5,4)
			,[3] = substring(v.Units,9,4)
			,[4] = substring(v.Units,13,4)
			,[5] = substring(v.Units,17,4)
			,[6] = substring(v.Units,21,4)
			,[7] = substring(v.Units,25,4)
			,[8] = substring(v.Units,29,4)

mansellan

isn;t there a sys view for locks?

(not a dba)

this

yes; google system catalog view sql server or dynamic management view sql server to see the list of all views avaialble

I think you want dm_exec_locks but my memory is iffy

it's also easy to just check DBCC OPENTRAN

mansellan

I'm so glad my current gig limits my SQL exposure to the odd CREATE TABLE

far too much voodoo in sql

this

9:54 PM

@MathieuGuindon did you mean to convert into a iTVF?

Mathieu Guindon

is that what I just did?

this

nope still mTVF

Mathieu Guindon

I meant to make it set-based

this

basically, if you do insert into @table, that's mTVF

iTVF is basically like a view... no procedural statements; only the SQL & parameters, that's it.

no BEGIN/END either.

Mathieu Guindon

but then what do I return if I just select?

need another type for the return table?

this

9:57 PM

npe

just return that result

e.g. ... RETURNS TABLE AS WITH (...) SELECT ...;

Mathieu Guindon

ah

like this?

create function dbo.NormalizeSizes (@values SizedUnits)
returns table as
	return
	with split as (
		select
			 v.SourceRowId
			,[1] = substring(v.Units,1,4)
			,[2] = substring(v.Units,5,4)
			,[3] = substring(v.Units,9,4)
			,[4] = substring(v.Units,13,4)
			,[5] = substring(v.Units,17,4)
			,[6] = substring(v.Units,21,4)
			,[7] = substring(v.Units,25,4)
			,[8] = substring(v.Units,29,4)
			,[9] = substring(v.Units,33,4)
			,[10]= substring(v.Units,37,4)
			,[11]= substring(v.Units,41,4)
			,[12]= substring(v.Units,45,4)

seems the return is required

this

yes I think so

i'm aircoding!

and that is then used to insert into a temp table with indices?

mansellan

huh, #til that .frx files are completely different formats between VBA and VB6

this

really?

why?!?

mansellan

VBA seems to use a (slightly modified) COM SF format, VB6 uses a (slightly modified) RES format

this

10:01 PM

both don't even follow the official formats either?

mansellan

no idea...

this

:facepalm:

mansellan

@this they're close

maybe some preamble. analysing now.

this

Still, if it's "slightly modified", that's lame-ass

mansellan

^

this

10:02 PM

but I guess 90s MSFT wasn't too big on standards & consistency....

They were too busy playing Civilization with the rest of us peons.

mansellan

lol

I guess the difference might originate in the different storage layers. VBA uses IStorage, VB6 uses actual files.

still, would have been nice if they'd used different extensions...

maybe .ufx for userforms

so - they were consistent where they shouldn't have been!

this

They're consistently inconsistent.

Mathieu Guindon

@this I was thinking of just joining on that rather than cross apply

this

That said, we still can peek at the header of the files (they have one, right?) and determining whether it's VBA's frx or VB6's frx.

@MathieuGuindon Yep with a iTVF, you can indeed now join it. My only uncertainty is whether the optimizer won't be dumb and evaluate the iTVF more than once. You can always test and see

Mathieu Guindon

right

this

10:11 PM

it should do the right thing but when queries get complex enough, it's liable to go :derp:

mansellan

@this yeah, just gotta try and work out how they're laid out

this

we really need to abstract the file handling.

Mathieu Guindon

hm why has the create function statement been running for 2 minutes now

wtf

3 minutes now

wth

this

uh, the fact that you can't open the type folder and you're stuck is very strongly indication that you do have an open transaction.

Mathieu Guindon

fml

this

10:13 PM

check DBCC OPENTRAN to find the offender and kill it.

Mathieu Guindon

well well well

kill 53

this

you did verify that 53 is you and not some random snot?

Mathieu Guindon

lol dbcc told me

this

good. I've been there before and I usually just want to be sure I'm not actually offing one of my clients. Makes for a fun phone call.

so I was working in my application then it suddenly blew up, throwing up random errors. I restarted and it start working again. What do you think happened?

Mathieu Guindon

woot, function created

the UDTT was created as part of the trx

was only missing readonly in the function's arg list

this

10:18 PM

yeah, that is a annoying detail I always forget. Esp. considering that there's no such thing as readwrite table-valued parameter.

Mathieu Guindon

hm wait hold on, I do need to populate a table first

this

(it's there because ~~Joke Celko~~ SQL Standard sez so.)

Yes, you do.

Mathieu Guindon

ugh

so that'll be a full table scan taking id and f22 from YYF641SAV

this

That is unavoidable.

Unless you can actually filter the id somehow.

Mathieu Guindon

		declare @bucketsIssued table (
			 id int not null primary key
			,Units nvarchar(80)
		);
		insert into @bucketsIssued
		select id, f22 from Staging.dbo.YYF641SAV;

this

10:22 PM

but to parse the f22, you must scan the entire set and normalize. No way around it.

Mathieu Guindon

and now I can just pass @bucketsIssued to my TVF

this

yeah

I also assume you have to do it again for the ordered & shipped

Mathieu Guindon

yeah

but at least there's no cross apply anymore

this

hopefully it does makes a difference. If not, you may need temp tables so that you cn index the normalized contents to help cut down on the work on joining the normalized data from the temp table to whatever you're joining.

Mathieu Guindon

the join is on an int PK, shouldn't be too fussy

scrolls to top of procedure, notices begin transaction ... comments it out

the moment of truth

Msg 137, Level 15, State 2, Line 208
Must declare the scalar variable "@etlStart".
Msg 137, Level 15, State 2, Line 226
Must declare the scalar variable "@etlStart".
Msg 137, Level 15, State 2, Line 239
Must declare the scalar variable "@etlStart".
Msg 137, Level 15, State 2, Line 254
Must declare the scalar variable "@etlStart".

lol

ok now what

Msg 206, Level 16, State 2, Line 73
Operand type clash: table is incompatible with SizedUnits
Msg 206, Level 16, State 2, Line 114
Operand type clash: table is incompatible with SizedUnits
Msg 206, Level 16, State 2, Line 143
Operand type clash: table is incompatible with SizedUnits

oh, missing a not null constraint

no, it's still complaining

ugh

CREATE TYPE [dbo].[SizedUnits] AS TABLE(
	[SourceRowId] [int] NOT NULL,
	[Units] [nvarchar](200) NOT NULL,
	PRIMARY KEY CLUSTERED
(
	[SourceRowId] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO

oh, clustered

this

10:33 PM

is the nvarchar(200) also correct?

Mathieu Guindon

		declare @bucketsIssued table (
			 SourceRowId int not null primary key clustered
			,Units nvarchar(200) not null
		);
		insert into @bucketsIssued
		select id, f22 from Staging.dbo.YYF641SAV;

it's technically 120 chars too wide, but shouldn't matter

this

no

Mathieu Guindon

declare @bucketsIssued SizedUnits;

this

DECLARE @bucketsIssued dbo.SizedUnits

Mathieu Guindon

aaaah

bingo

this

10:36 PM

I guess you nwo have to wait to see how exactly glacial it is. :)

Mathieu Guindon

got an execution plan for the @readyUnits insert within 10 seconds

but the ready units is just a count aggregate

this

be interesting to see whether making them memory optimized / natively compiled makes significant difference.

my hunch is that this is a very good candidate for native compilation

Mathieu Guindon

(4303 row(s) affected)

(1 row(s) affected)

(362622 row(s) affected)

^ within 2 minutes

that's already at least 48 minutes faster than yesterday

this

Nice!

Mathieu Guindon

yesterday after 51 minutes all I had was "(4303 row(s) affected)"

this

10:39 PM

yeah - mTVF really hurts.

Mathieu Guindon

4 minutes

this

wouldn't surprise me if it was actually re-executing the TVF several times for same row X for each row in the table

(the original mTVF, I mean)

Either way, it does sounds like you just bumped the speed setting from glacial to 3-toed sloth. ;)

Mathieu Guindon

gotta go

@this thanks a bunch!

Duga

11:10 PM

[rubberduck-vba/Rubberduck] mariaecms starred us

2

Duga

11:22 PM

[rubberduck-vba/Rubberduck] IvenBach added label PR-Status: WIP to pull request #5357: Issue5346 extract interface creates private classes

[rubberduck-vba/Rubberduck] codecov[bot\] created comment on pull request #5357: Issue5346 extract interface creates private classes

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/5357?src=pr&el=h1) Report
> :exclamation: No coverage uploaded for pull request head (`Issue5346_Extract_interface_creates_private_classes@664c7d9`). [Click here to learn what that means](https://docs.codecov.io/docs/error-reference#section-missing-head-commit).
> The diff coverage is `n/a`.

[rubberduck-vba/Rubberduck] build for commit 664c7d99 on unknown branch: AppVeyor was unable to build non-mergeable pull request

BUILD FAILURE!

IvenBach

^ Any ducks have an idea of root cause and are willing to breadcrumb me to it?

Vogel612

non-mergeable apparently

conflict in RubberduckUI.resx

IvenBach

:derp: I didn't fetch from upstream or run unit tests locally.

Besides that. The behavior not honoring the public class and allowing a private interface to still be extracted.

IvenBach

11:41 PM

mumble mumble github.com/IvenBach/Rubberduck/blob/… doesn't export a file. for unit tests.

FreeMan

@this Sunspots. Always blame sunspots.

Duga

[rubberduck-vba/Rubberduck] IvenBach synchronized pull request #5357: Issue5346 extract interface creates private classes

[rubberduck-vba/Rubberduck] codecov[bot\] edited comment on pull request #5357: Issue5346 extract interface creates private classes

> # [Codecov](https://codecov.io/gh/rubberduck-vba/Rubberduck/pull/5357?src=pr&el=h1) Report
> :exclamation: No coverage uploaded for pull request head (`Issue5346_Extract_interface_creates_private_classes@79540a4`). [Click here to learn what that means](https://docs.codecov.io/docs/error-reference#section-missing-head-commit).
> The diff coverage is `n/a`.

M.Doerner

11:56 PM

@IvenBach To be able to test the refactoring, you will have to use an abstraction to do add the class via a file.

Transcript for