Maybe the side effect that caused your issue?

Try rotating 90 degrees. Now it's in front of you, not to the side.

sometimes I know what I'm talking about. I often don't, but sometimes, I do.

that comment felt better than a checkmark :)

[rubberduck-vba/Rubberduck] Inarion commented on Rubberduck.Core/Properties/Settings.settings of pull request #4612: Expands description of ShadowedDeclarationInspection

[rubberduck-vba/Rubberduck] retailcoder commented on Rubberduck.Core/Properties/Settings.settings of pull request #4612: Expands description of ShadowedDeclarationInspection

ok, that query running for 10 minutes isn't cutting it

10 minutes? Wow.

Unrelated: I know this might got lost in shuffle for the 2.3.1 hotfix but I do need volunteers to test whether the deployment PR will build cleanly....

I may have combined 6 Sage databases into one

Project I'm working on now runs ~400K queries in roughly 10 minutes.

Granted, the server is self hosted, but the SQL is wicked stupid.

TTGRTQW.

heterogeneous queries tend to be a major drag.

I'm missing so many indexes...

then there's this:

	where items.CATEGORY=isnull(@category,items.CATEGORY)
		and items.SEGMENT1=isnull(@style,items.SEGMENT1)
		and items.ITEMBRKID='FG'
		and (@collection is null or [collection].VALUE = @collection)
		and (@season is null or ssn.VALUE = @season)
		and (@filter is null or items.SEGMENT1 like '%' + @filter + '%')
		and items.SageDb = @db
	;

ooof

"season" and "collections" are optional fields stored in ICITEMO

I'll see that and raise you this:

SELECT CLID,CMID,CSSOURCE,CSSESS,CSTRNS,CSTRNDT,CSBILNUM,CSBILTYP,CSAMNT,CSSRC,TKID,CRALCAMT,CSRVEFFDT FROM
(
	SELECT C.CLID,C.CMID,CSSOURCE,CSSESS,CSTRNS,CSTRNDT,CSBILNUM,CSBILTYP,CSAMNT,CSSRC,TKID,ROUND(CSAMNT * CASHPRCNT / 100, 2) AS CRALCAMT,CSRVEFFDT
	FROM CMDCSHREC C
	JOIN CMDBILD B ON B.CLID = C.CLID AND B.CMID = C.CMID AND B.BLSTMTNUM = C.CSBILNUM AND B.BLTYPE=C.CSBILTYP AND B.BLRECTYP=C.CSRECTYP AND B.BLRVEFFDT = '19000101'
	JOIN CMDCRALOC A ON A.CLID = C.CLID AND A.CMID = C.CMID AND A.BLSTMTNUM = C.CSBILNUM AND A.ATTPABBR = 'WORK' AND A.RECRDFLAG = 'Y'

This is what happens when I'm in a hurry.

FWIW, that might be a case where building it dynamically may be better.

@Comintern you like to scream a lot, do you?

Nah, it's cased like that in the table and I hit tab a lot.

it is a pet peeves of mine, though - if they're all in scream cases then it just becomes blurred wall of text. I usually prefer SQL keywords in scream cases and identifiers in normal case.

so the results start streaming in pretty much immediately, but after 60 seconds I only have 2K rows fetched

wth

This schema might tempt me to write SQL keywords in lower just to get that distinction between keywords and identifiers.

That's one of the benefits of never having to look at it again when I'm done.

@MathieuGuindon wtf is your DBMS doing?

WORN code, eh?

@Vogel612 at this point, I'm waiting for the execution plan, to find out

execution plan taking more than a few millis to generate???

typically if you ask for actual plan, you don't get it until query finishes.

for the actual execution plan to be generated I need the actual query to run, no?

^

3:45, 5.5K rows

still going

just to clarify - is this in fact a heterogeneous query?

what's a heterogeneous query?

querying different tables, different indices, different databases

any query that joins remote databases

no. it used to be, but I moved all the tables over to my SQL instance

e.g. whenever you are using 4-part names or OPENROWSET or OPENQUERY

the tables are exact replicas of the Sage tables (plus a SageDb and a DateInserted column), so none have indexes other than a composite PK

@Vogel612 only database determine whether a query is heterogeneous; joining different tables within a single database doesn't make it heterogeneous.

hum... what about unions across different tables?

same thing.

#TIL

7:30, 14K rows, still going

fml

e.g. the data sources for all tables are same (aka homogeneous)

killing it. hopefully the estimated query plan provides some insight

given that you have no indices, I don't see what else it's going to do besides scanning them.

> The Query Processor estimates that implementing the following index could improve the query cost by 67.0289%.

it's the pricing info that's killing it

oh, another

> The Query Processor estimates that implementing the following index could improve the query cost by 73.5981%.

damn

now completes in 4 seconds

38.7K rows

lol I'd have waited another half hour for the execution plan

now step 2, the sales

> The Query Processor estimates that implementing the following index could improve the query cost by 95.0122%.

unreal

You on step 3) Make it fast?

at that level of "fast" it's more on step 1) make it work

^

ticket literally says "SSRS not working" lol

That's the hidden Step 4) Goto Step1

especially if it's as easy as slapping a few indexes on columns

yup. purchase orders, same

> The Query Processor estimates that implementing the following index could improve the query cost by 96.7801%.

if you didn't have any indexes before that's not really surprising

You weren't the one who originally wrote these queries?

yes

you can turn a repeated O(n) into a handful of O(log n) operations with those

@IvenBach the queries aren't necessarily the problem here

The lack of indices is what's making them slow.

oh crap

the "script as create" tool doesn't script the indexes.... that's where things went wrong

Mugs going to get the queries running so fast they'll be done before he sends the request.

ow

then the inventory... and the rest is windowing functions and aggregates

Wait wut? It should be scripting the indexes assuming your SQL Server is relatively recent.

2014

and drop+create doesn't script them either

2016 here, but I seem to remember seeing them in earlier ones.

dafuq

i.e. I should have gone through the stupid Task -> Generate Scripts... wizard

or, I can go "generate scripts" now, script all the indexes off the tables I copied, slip the SageDb column in, and done

awesome, there's a table in the AR module that's literally named ARPOOP

lol

That's much worse than APPOOP. Don't know if you want to be receiving that.

My personal favorite is still CNTBTCH though.

of course "Script indexes" is false by default. Thanks SSMS.

@Comintern that's... something

Like the vowels would hurt there... BatchCount is much much better.

wtf does that mean WITH ( ONLINE = OFF )

on a DROP INDEX statement

I think that locks the index.

> In ONLINE mode the new index is built while the old index is accessible to reads and writes.

weird that there was only 1 such

Huh.

I wonder if it's an order of operations thing - like it's dropping something else that would thrash on the index removals.

so, from 2:55 for 1830 rows, we're now at...

oh wait no, that wasn't the full thing

ugh

8 seconds

<10 seconds, good enough for me

TTQW

ahh i get to feel nice.

i just wrote my first stored procedure in sql server completely from scratch

the only bits i didnt type is the fields for the insert queries.

essentially i have this set of tables that keeps a snapshot of the test procedures for our parts, and if something super borks itself (like it did today) we can recover. well i just wrote the procedure that actually resets the data to a specified snapshot

i feel really good

though that might be my perscription migraine meds.

@MathieuGuindon it's a settings you need to specify via the options and/or the Advanced button

Did the CE ever respond to code pane focus events? I can't find any indication that it's listening to anything that would provide those events.

It sets focus just fine.

Im thinking no it never has.

Huh. Shouldn't be hard to wire up - we're already raising those events for the toolbar.

Although a "Sync with active document" button would be completely bad-ass.