Newbie coder here who may or may not be creating too much work for simple tasks. So far it is working great, but I was wondering if I could optimize a little since I have nearly 30 variables on a code of only 100 lines.
Since I'm not a coder, I code based on how I would be doing it manually:
...
I have been coding vba for quite sometime now and it is only recently that I have begun diving into doing up some class module.
some questions on my mind is when should I stop including functions and properties in a class ?
i.e. I am creating a class module where by it record table properties...
If @RubberduckVBA let you import modules/forms directly from other Excel, Word or Access VBA projects, _without_ the need to open that project or firstly export from that project, would that be useful?
@FreeMan IME, the bigger the corporate is, the more likely it's a rogue. In a small business with no IT staff, it's their LOB and they do whatever the hell they want.
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.
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.
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?
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.
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
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.
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
)
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...
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.
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"
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
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
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
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.
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%...
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?
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
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?
Data loads could probablypossibly 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 .
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
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.
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.
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.
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.
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...
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)...
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.
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.
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.
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.
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.
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!
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.
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.
Dim cmd As ADODB.Command
Set cmd = cat.Procedures(QueryName).Command
With cmd
Set .ActiveConnection = cat.ActiveConnection
Dim N As Long
For N = LBound(params) To UBound(params)
.Parameters(N).value = params(N)
Next N
Dim rs As ADODB.Recordset
Set rs = .Execute
End With
usually you do Set rs = New ADODB.Recordset : rs.Open cmd
You then have more control over how the rs should be (whether it needs to be client side or server side, use forward only or static or keyset, whether it's readonly or not)
I don't really like ADO because of its apparent complexity. Doing the same thing in DAO was way easier - until I got to a point where I couldn't accomplish something with DAO and had to use ADO.
@KySoto I wrote code that would check the version number in the 1 local table in the Access DB. If it didn't match the version # hard coded in the code, it would boot them out. Then, I wrote a .cmd that would copy the read-only .accdb front-end from the network to their local & execute it for them. Once I got everyone using the .cmd, I had no more issues!
@FreeMan More seriously, the recommendation are usually helpful in telling you that the query isn't optimal but you need to look at what it's saying and compare it to the shape of the execution plan and how you generally will use queries to make the decision yourself which might end up being a subset or superset of the index. in some case it's something completely different.
I'm trying to figure out why it recommended the INCLUDE([ClinicID]) which is a pretty significant portion of the query (the JOIN is on ClinicID), and the order in which it specified the columns doesn't make much sense.
It's a nice starting point for those of us wandering in the woods with our eyes still shut...
It might be that it realized it was suboptimal when it was performing an operator already deep in the plan, and based the recommendation on that operator --- all previous operators were "optimal" but ultimately giving answers that we didn't ask for.
IF that is the case, you need a brand new plan shape, not a mere tweak of some existing plan shape.
probably didn't help much that I wasn't paying enough attention and created 3 indices on one table. It was supposed to be 1 index on each of 3 tables... :/
@FreeMan the way i went about updating is about 95% of the people here use a desktop icon to launch the access databases they use. so i created a powershell script that does the auto updating
whenever i log a change into the changelog for a given application
when hte script would run, it would compare the latest version number in hte change log table with the last logged version of the application
sounds about the same - I created a desktop shortcut that looked like what they were used to clicking on. I just had it do a bit o' extra magic behind the sceens.
wow! the 82ms query went to 69ms with the addition of those indices!! Much more improvement and now the leader in the "will it blend?" "will it make it into the Stored Procedure?" sweepstakes!
@KySoto click the 'Fixed font` button ---> that appears over there when you paste in anything multiline
@KySoto Because... reasons. :/ The naming of things (business language) is not completely set in stone. And when I have column A, which refers to thing X and column B which refers to thing Y and later on the name A suddenly refers to thing Y, then it seemed appropriate to have somewhat proper naming. (As to prevent total confusion on my part later on)
most of the time it's my team lead and me making all the decisions - then it's fine. But sometimes the clients want something else and then it can get trickier. ;) (Or as in the mentioned case: nothing was defined and I was just naming things as it appeared reasonable to me. The decisions came later on.)
Newbie coder here who may or may not be creating too much work for simple tasks. So far it is working great, but I was wondering if I could optimize a little since I have nearly 30 variables on a code of only 100 lines.
Since I'm not a coder, I code based on how I would be doing it manually:
...
2018/07/01 00:00:26 +08:00,BPRO:MSTS:SMODE,1401,11621660,,2018/07/01 00:00:25.980 +08:00,FCS0102:%DR0060S010201,3:0:0,FCS0102,HS1298,,HS1298 BY OPERATOR VALIDATION AUT
2018/07/01 00:01:38 +08:00,BOPE:MPRO:SDATA,1601,11621664,ROCC_SENIOR,2018/07/01 00:01:38.000 +08:00,FCS0102:%DR0178S0102...
I have been coding vba for quite sometime now and it is only recently that I have begun diving into doing up some class module.
some questions on my mind is when should I stop including functions and properties in a class ?
i.e. I am creating a class module where by it record table properties...
VBA Rubberducking
VBA Rubberducking