DB Design & Optimization with @this

FreeMan

Aug 17, 2018 11:40

@this I've got 2 variations on the same query. What tool do I use in SSMS and how do I use it to determine which is more efficient?

I presume SQL Server Profiler under Tools...

this

No. two strategies:

1) Use SET STATISTICS IO ON; and SET STATISTICS TIME ON; -- then evaluate what gets you the less reads in less time

Ultimately, it's the wall clock that rules, right?

FreeMan

maybe... if you're banging the heck outta the server to save a couple of ms and preventing others from doing anything, maybe not?

this

2) Use execution plan (Show Actual Execution Plan). This tells you about how SQL Server is actually retrieving the data. That requires a bit more knowledge to tell whether it's doing this in most efficient manner possible.

The point is that even if a query had a fugly execution plan, if it executes faster and does with fewer reads, then that's the query you want to back.

Note: don't use both at same time; use one or other independently. Otherwise, it skews the statistics you see.

FreeMan

and now to interpret the tea leaves...

I've implemented option 1) (since you typed it first and it was quick & easy:)

this

BTW, there's also new feature in newer versions --- Live Client Statistics which gives you an animation of the execution plan being executed. But I find it of limited use for quick queries since it's already over by time you run it.

FreeMan

Aug 17, 2018 11:47

the first query is an outer SELECT from a UNION of others and gives me this:

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 47 ms, elapsed time = 48 ms.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

the second version does each of the formerly UNIONed queries separately, then does the math on the stored results and gives me this:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 5 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

which makes it harder to sort because there are several little steps that need to be added up, right?

this

Yes you could add them up, at least for hte elapsed time.

One thing that smells to me is that you're reading the tables 3 times over in the 2nd query

e.g. you get this 3 times:

FreeMan

Looks like the 2nd version comes up to 71ms of elapsed time in total, while the first one is 82ms elapsed.

this

Table 'SatSurveyV1'. Scan count 1, logical reads 1224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

FreeMan

I did see that... I'm not sure I understand why. The first query is something like:

this

Even though you got much more reads, I bet that it came out quicker because it was less work to scan 3 times for 3 simple predicates than to scan once for complex predicates.

FreeMan

Aug 17, 2018 11:53

SELECT ...
FROM  (
  SELECT ...
  FROM tableA
    UNION
  SELECT ...
  FROM tableA
    UNION
  SELECT ...
  FROM tableA
)

this

But to understand why you have to see the execution plan.

uh, are you literally UNIONing with no WHERE clauses? Making it ~~return~~ evaluate same rows 3 times?

FreeMan

@this No, sorry, that was a gross oversimplification - there are 3 distinct WHERE clauses for each of the UNIONed sub-queries

this

Ok, now are each subquery a distinct subset, or can they overlap?

e.g. a row could qualify in two or more subqueries and you need to dedupe?

FreeMan

They'd better be distinct subsets!

selecting distinct ranges on a simple int column

this

if you are sure they must be, you can save the server some work by changing to UNION ALL

with a UNION, it is equivalent to adding DISTINCT to the final outermost query; server has to check each row isn't a duplicate of some other row. More work

with UNION ALL, there is no de-duplicate and thus it will run faster

FreeMan

Aug 17, 2018 11:58

because ALL eliminates the de-dupe during the UNION process and if I know there are no dupes to begin with, then no reason to make the server do the extra work...

yeah, you just said that, but faster...

that dropped the total execution time from 82ms to 80ms.

this

W00t! 2 ms saving! :D

FreeMan

If I was running this 100,000x per hour, I might consider that a huge win, but since it's running ...

well, it's going to be called quite a number of times in this little project, but not 100k times...

this

so the other version of the query that doesn't union seems faster still.

FreeMan

despite the extra table reads

this

it's doing what? 3 separate SELECT?

into a temp table?

FreeMan

Aug 17, 2018 12:02

no. Gross oversimplification:

SELECT @varA = count(*)
FROM tableA
WHERE A
SELECT @varB = COUNT(*)
FROM tableA
WHERE B
SELECT @varC = COUNT(*)
FROM tableA
WHERE C
SELECT @result = MATH(@VarA, VarB, VarC)

The UNION query is (gross simplification):

SELECT  @result = MATH(A, B, C)
FROM  (
  SELECT ...
  FROM tableA
  WHERE A
    UNION ALL
  SELECT ...
  FROM tableA
  WHERE b
    UNION ALL
  SELECT ...
  FROM tableA
  WHERE C
)

this

hm, they aren't equivalent.

with the UNION ALL, you get 3 rows on top

FreeMan

they produce the same @Result!

this

with the variable assignment, you get 1 row, which is then assigned to a variable

are you doing any aggregation on the union?

FreeMan

SELECT @result = MATH(SUM(A),SUM(B), SUM(C)) with the appropriate Group By at the end

told you it was a gross oversimplification. That part was perfectly clear to me!

Quack Exchange

0

Q: Conditional highlighting (Font size colour, size and Bold) using VBA

Note: Question originally asked in SO, here I have code which achieves exactly what I want. The basis of my code comes from the generous help of Tim Williams in a previous question. Building on his help, I have added slightly to the functionality (larger font size, and returning formatting to ori...

this

Aug 17, 2018 12:08

Aggregation is probably what killing you

in the 2nd version, there is no aggregation so the server does not have to go through the motions of grouping and streaming the data.

(however few rows there may be; setup for aggregation can be expensive when dealing with small amount of rows)

FreeMan

hmmm.... this was originally some code stored in a .sql file that I open & execute in SSMS for testing/debugging/finding-why-the-heck-the-number-doesn't-look-right purposes, and I just stuffed it into what is to become my Stored Proc.

this

I think that if you compare the execution plans, it might help illustrate

btw was that your CR q?

FreeMan

I'm pretty sure, now that I think about it, that I can get rid of the aggregation from that query except at the outer select where it's only got 3 sub-result rows to aggregate: "x, 0, 0", "0, y, 0" and "0, 0, z"

Yup - how do I make this sucker better.

this

sorry didn't get to it yet

FreeMan

suckered ya in, didn't I! :)

I seem to be reasonably good at writing CR questions that get upvotes but no answers... :(

this

Aug 17, 2018 12:12

but anyway, yes it sound like 2nd query works better because you get to avoid the superficial aggregation since there can be only one result per column.

the reads bother me a bit -- it's indexed, right?

well it might be but server may decide it's useless for its needed and deciding to scan the whole table

(and that is where the execution plan comes in)

FreeMan

oooh, yuck! that went up to 113ms!

Yes, index, but it might not be on the right column(s)

scurries off to figure out how to get the execution plan. Will return with pretty pictures.

hmmmm... From the first query:

and from the 2nd query:

I'm guessing a new index might help! :)

this

it sounds like it would but you need to consider the whole shape of the plan and how it will be used by other queries.

you don't want to just make every index for every query there is - that would be awful

FreeMan

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

this

if it's a critical query, it might be worthwhile making a specialized index for that query. But if it's one of 5 similar queries, you get better net benefits by providing an index that cover all 5 queries

FreeMan

that's for the "3 selects into variables, then math for the output"

this

Aug 17, 2018 12:24

FYI I rarely use the recommendation straight

FreeMan

yeah, well, you've written a book on the topic, I'm a DBA without training by necessity

this

I know. This is also unfortunately more of an "art" than a "science" so it's not easy to give you pat answers.

I think the best thing you can do for yourself is to experiment! Implement it straight, study how the shape of the plan changes, and try some variant on the index structure

FreeMan

Actually, I make a bunch o' queries from code that will be looking at those columns, and I'm thinking that it'll be easier in the long run to convert those to SPs so that, eventually, I can move this from VBA to C# (might as well put those skills I'm gonna learn to use somewhere), without having to rewrite & test all the queries again.

this

pay attention to the operators being used on the right as it will tell you what it really want to have in an index.

Yes, stored procedure is always good way to encapsulate all those messy database accesses which no programming languages has business toying with.

FreeMan

based on the types of queries I make, having ClinicID, and CollectionDate, at the bare minimum in an index would make a lot of sense..

this

Aug 17, 2018 12:29

You'll have to try that.

you might find out that server may snub its nose at your index and scan the table anyway because it figures it's faster to do that way.

FreeMan

well, I'd go kick the server if it did that! If I had any clue whatsoever where it actually was... I do know that it's running as a VM somewhere, but I don't even know where we keep our physical hardware. I think it's a few blocks up the road...

My 2nd query with the 3 separate SELECTs then math at the end gives me this three times:

well, for 2 of the queries, it's 72%, for the 3rd it's 75%...

come on little fella... you can upload...

it's only 40k. You can do it!

this

Aug 17, 2018 12:44

A shy guy, eh?

FreeMan

urg, still hasn't uploaded that image yet??

take 2:

well, that worked a treat.

If, for example, I have an index with 4 columns in it, but this particular query only references 3 of them in the WHERE clause, will it still be able to use that 4-column index?

this

Yes, provided that the 3 columns reference are the same order as the first 3 columns of the index

FreeMan

hrm... that means I really need to focus on how all the queries in the package are written in terms of WHERE clause order?

this

i would spend more time looking at the lines, not the %s. You have a very thick lines going in and a thin line going out in 2 or 3 places. That's a smell to be investigated.

It's not just the WHERE

if you have an ORDER BY that will figure into the index use

Ditto with GROUP BY

heck, even the ON clause for a join matters, too!

FreeMan

so WHERE, GROUP BY, ORDER BY and JOIN ... ON column specifications need to be in the order that the columns are defined in the index in order for the engine to use that particular index?

this

Aug 17, 2018 12:56

no. #ItDepends

FreeMan

sigh... :(

this

That's why I told earlier best thing you can do for yourself is to experiment and play with different index and looking at how the plan shape change

FreeMan

by "Experiment" you mean creating and dropping various indices to see what works best?

this

after a bit of experiment, you will develop a instinct for which index you need to fix a query (or queries).

Yes

FreeMan

got it.

this

Aug 17, 2018 12:57

Keep in mind that it can only go so far, too

FreeMan

@this so the thicker the line, the more... what?

this

for example, if the table design is suboptimal, no amount of indices may help you.

thickness of line => more rows

FreeMan

@this I shouldn't have an index on every possible column combination?

@this thx

this

no, not if you are building a OLTP database.

FreeMan

this is definitely not that!

We load data monthly then do monthly & quarterly reporting.

this

Aug 17, 2018 12:59

It's common in a OLAP database (aka data warehouse) but it's only because it's not normally written to anytime, anywhere as OLTP is.

ahhh, so it is a OLAP. Then you don't care about how slow it takes to load a data

(up to a point, at least)

In that case, you can go crazy and make all indices you want

FreeMan

Data loads could ~~probably~~ possibly benefit from DROP INDEX... load data ...CREATE INDEX. We did do that at a former employer (ORACLE 8 was new then). as we loaded millions of rows to the data warehouse monthly .

this

Possibly. You know the load more than I do.

FreeMan

but I'm really only talking a few thousand rows a month

this

if it's only a small %, reorganizing/rebuilding indices might be sufficient.

FreeMan

@this YAY!!! :D:D:D

this

Aug 17, 2018 13:02

the point being, in OLAP database, it's totally OK to have 20+ indices on a single table. in OLTP, that would be murderous.

FreeMan

because massive, real-time inserts into all those indices would be a performance killer, right?

this

yep. remember that the indices are always organized, so there's always a tree balancing going on

FreeMan

coolio.

@this back to this real quick... You're talking about the red arrows?

In this case, that makes sense, to an extent - I'm only getting a single COUNT(*) out of this particular query...

this

Aug 17, 2018 13:15

Yes. Generally speaking, if you see too much rows flowing deep into the plan, it's a sign that it's not being filtered out early enough and thus creating more work for the server. Also, it should be compared to the cardinality estimate; if it estimates 1000 rows but the plan show 10000000 rows then something is horribly wrong. The server rely on good cardinality estimate to accurate gauge the true cost of performing an operation.

in case you didn't find it already; you can click on a line and it will show you how rows went through

FreeMan

nifty. I'd noticed that pops up when you hover over an icon, hadn't realized it was there for the lines, too.

Ugh. Just realized that I've got a PK on the unique auto-increment ID column, but nowhere else on the survey rows... that's not gonna help.

Thanks for the input, @this. Very helpful! No direct answers, but lots to think about, and those really are the best answers.

this

yw!

FreeMan

Aug 17, 2018 13:46

heh... just realized that 70+% of the time doing a table scan is the table that holds the oldest data that will not be included in this query because of the date range I've selected, however, the table is included in the view that I'm selecting from.

And it will likely never be included in any future queries because we're likely not going to report on anything that old.

how's that for throwing a wrench at the monkey?

this

That's why I do not like using views in other views. It always ends up badly.

I get it - views seems like a good thing since they save you from repeating yourself, right? But what works well as a programming principles simply don't apply in the world of SQL.

In C# or VBA, we want a generic reuseable object. In SQL we want a unique snowflake.

FreeMan

OK, how do I work around this situation w/o changing code every year:

Our survey changes (we try to keep it to 1 change/year). Questions are added and dropped. I chose to create new tables to keep the new survey in instead of having a multitude of columns, many of which would be NULL for older/newer surveys.

this

This is a data warehouse, right?

FreeMan

The view selects the columns most needed for the most common reporting needs so that I can select what I need across 2 years of surveys for "this year" vs "last year" reporting without having to change code every year.

this

So, I assume you have a set of queries that applies for all surveys. Yeah.

Comintern

Aug 17, 2018 13:51

@FreeMan Can you normalize the source, or is that off limits to you?

FreeMan

yes, but don't tell IT that - they have a data warehouse and got really bent outta shape when we called it that. Now we call it the "database" and it eliminates political issues. sigh...

this

WTF

I guess they insist on calling yellow blue and red green.

But anyway - what about of Comintern's question?

FreeMan

big hospital system. Our data is a tiny subset of it that they really don't even know exists. We've got our own SQL server (hence me cowboying the DBA role)...

this

Gotcha. So, can you consider doing a ETL instead?

FreeMan

@Comintern Not sure, exactly, what you mean by that?

I do have full control of the server, I can change table structure willy-nilly until I'm in a really, really deep hole...

this

Aug 17, 2018 13:54

With a good data warehouse, you will want to have a fixed schema, generally a star or snowflake schema; you could dump the extra columns into a separate table.

FreeMan

ah, gotcha.

hadn't thought of that.

this

so use SSIS or something to transform the source data

and then you have a stable schema, with one extra "junk drawer" table

Comintern

If you're running the DB, I'd restructure your data. Survey table that hold dates, etc, a Questions table (instead of your columns) with a M-M with Survey, and then a Responses table.

this

then query optimization becomes much easier

Comintern

^^

FreeMan

Aug 17, 2018 13:55

Load the core columns into the primary table, then the extra, 1-year only questions go into related table that holds only those.

Comintern

Nope, not even the core data. That's a view.

What happens when the requirement on what a "core" question is changes?

this

@Comintern This is good for OLTP. Does it make sense when it's a OLAP, though?

Comintern

It certainly makes managing it easier.

this

I guess @FreeMan will have to make the call - if he's dealing with millions and millions of rows of data, reducing the number of joins can do wonder for reporting purpose.

FreeMan

so table Questions holds all the questions ever asked, table Survey is a list of questions and the date range those questions were asked, and table Answers is a list of answers to the questions, including some sort of survey-unique ID so that I can reconstruct a single survey from the long list of seemingly random answers.

this

Aug 17, 2018 13:58

But of course, denomralizing means painful decisions like deciding if a question is core or not.

Yes, exactly, @FreeMan

FreeMan

so far, I've got 24k rows of data for all surveys. Not looking at millions of anything.

this

that way, you don't have any NULLs, either. Only a set of answers per a suervey linked to a set of question.

Comintern

^ IME it makes the indexing decisions easier too.

this

Go OLTP then. That is helpful when you don't know if a question will be a core one or not.

since you have more flexibility.

the star schema with denormalized structure only works well when you have large amount and you know exactly what you are going to do with it.

FreeMan

hmmm... never thought of that. Did do exactly that at a previous place, though.

Comintern

Aug 17, 2018 14:00

My philosophy is that anything that can change in its design should generally be a row, not a column.

FreeMan

that'll be a huge undertaking...

@Comintern wish I'd thought that far ahead 3 years ago...

this

@Comintern Yeah, good summary

Comintern

Normalizing really isn't that hard - just use pivots and unpivots to select into new tables.

FreeMan

@Comintern off to google again...

Comintern

I have some examples on a VM at home. I can load them up somewhere if you want.

FreeMan

Aug 17, 2018 14:03

now, with 24k rows of current data (and a 100 or so new rows a month), I'm not sure this will really be worth it, but as a learning experience it's probably invaluable.

this

are all data access via views or stored procedures?

Because if they are, you could basically change your database and still provide backward compatibility to the applications

(but get some tSQLt tests in place before you do this; otherwise you'll never prove that your changes didn't break something)

Comintern

Even if they aren't, you can create views based on the old table definitions.

this

^

FreeMan

@this oh heavens no! That would have been the really smart thing to do. All those queries are embedded in VBA everywhere. :( runs for his life

this

and that's why you don't write SQL outside the database

Comintern

Aug 17, 2018 14:06

^

FreeMan

However, making these changes in the Dev environment, I can write SPs to cover all the necessary conditions, then change the VBA to use said SPs and feel like the hero nobody will ever know about!

this

are you binding forms?

or just for reporting/readonly?

FreeMan

it's all read only reporting.

Inarion

I'm sorry for rudely interrupting... I'm having a (to me) seriously confusing problem with parameterized queries in Access in combination with VBA/ADO:
There are a couple queries in Access: insert, update and select queries. All of them with parameters. They work exactly as intended when testing them via the GUI.
Then I have a function that is meant to ease the use of those queries: `ExecuteParameterQuery(QueryName, params())`. It flawlessly works with the insert and update queries. And horribly fails at any select query.

FreeMan

the only interaction in this mass is selecting which report you want to run. All my forms are nothing but buttons.

this

Aug 17, 2018 14:08

Ok. Whenever you are binding forms for editing data; they need to be using a view as its source, too; no joins. It's OK if you apply some client side filtering/sorting but it need to be a single view for best performance.

FreeMan

files ^ that away for future reference

VBA Rubberducking

Participants