\u201cWhy are you running this query?\u201d I politely enquire.
The user thinks, “Why run the same query over and over again to get the data that I want to explore and present in different ways. Let just get one large cut and then manipulate it on the client.\u201d<\/p>\n\n\n\n
What users don\u2019t realise that dealing with a huge amount of data on the client side is difficult. Exporting data out of the query client, into an application like excel can be frustrating with memory and CPU limitations compared to a spec\u2019d-out server. Plus, Excel is not going to handle 8 billion rows.<\/p>\n\n\n\n
The requirements of queries like these needs to be analysed \u2013 do the users need that atomic level data? Can strategic aggregation tables be provided to the user, so they have more manageable data to return. Can different technology like OLAP cubes be built on top of large tables to answer the user\u2019s needs?<\/p>\n\n\n\n
The user has duplicate in their results and don’t know why. Instead of investigating why there is duplication (usually result of a poor join), they just slap a DISTINCT at the top of the query and call it done. Here is a simplified example that I regularly come across:<\/p>\n\n\n
\nDECLARE @ACCOUNTS TABLE\n(\n\tORG INT,\n\tACCOUNT_ID INT,\n\tBRANCH INT,\n\tBALANCE NUMERIC(18,2),\n\tPRIMARY KEY (ORG, ACCOUNT_ID)\n);\n\nINSERT INTO @ACCOUNTS VALUES(1, 18, 60, 50);\nINSERT INTO @ACCOUNTS VALUES(1, 19, 60, 150);\n\nDECLARE @BRANCHES TABLE\n(\n\tORG INT,\n\tBRANCH INT,\n\tBRANCH_NAME VARCHAR(20),\n\tBRANCH_STATE VARCHAR(3),\n\tPRIMARY KEY (ORG, BRANCH)\n);\n\nINSERT INTO @BRANCHES VALUES(1, 60, 'Branch of ORG 1', 'VIC');\nINSERT INTO @BRANCHES VALUES(2, 60, 'Branch of ORG 2', 'VIC');\n\n\nSELECT \n\tA.ORG,\n\tA.ACCOUNT_ID, \n\tA.BRANCH,\n\tA.BALANCE,\n\tB.BRANCH_STATE\nFROM @ACCOUNTS A\nJOIN @BRANCHES B\nON\t-- B.ORG = A.ORG\t-- User forgot this predicate in the join\n\tB.BRANCH = A.BRANCH;\n<\/pre><\/div>\n\n\nResults:<\/p>\n\n\n\nORG<\/strong><\/td>ACCOUNT_ID<\/strong><\/td>BRANCH<\/strong><\/td>BALANCE<\/strong><\/td>BRANCH_STATE<\/strong><\/td><\/tr>1<\/td> 18<\/td> 60<\/td> 50.00<\/td> VIC<\/td><\/tr> 1<\/td> 18<\/td> 60<\/td> 50.00<\/td> VIC<\/td><\/tr> 1<\/td> 19<\/td> 60<\/td> 150.00<\/td> VIC<\/td><\/tr> 1<\/td> 18<\/td> 60<\/td> 150.00<\/td> VIC<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nThe user looks – “Oh Dear – I’ve got duplicates! Let’s get rid of them.”<\/p>\n\n\n
\nSELECT DISTINCT\n\tA.ORG,\n\tA.ACCOUNT_ID, \n\tA.BRANCH,\n\tA.BALANCE,\n\tB.BRANCH_STATE\nFROM @ACCOUNTS A\nJOIN @BRANCHES B\nON\t-- B.ORG = A.ORG\t-- User forgot this predicate in the join\n\tB.BRANCH = A.BRANCH;\n<\/pre><\/div>\n\n\nThis poses many problems:<\/p>\n\n\n\n
\nThe Database must work harder using incomplete joins on indexes to bring across the data; and then work harder supressing the duplicates<\/li>\n\n\n\n If the field list change; then the distinct might not work anymore.<\/li>\n<\/ol>\n\n\n\nFor example, with the above query \u2013 if the user brings in the column \u201cBRANCH_NAME\u201d then the duplicates return:<\/p>\n\n\n
\nSELECT DISTINCT\n\tA.ORG,\n\tA.ACCOUNT_ID, \n\tA.BRANCH,\n\tA.BALANCE,\n\t--------- Add in new column ---------\n\tB.BRANCH_NAME,\n\t-------------------------------------\n\tB.BRANCH_STATE\nFROM @ACCOUNTS A\nJOIN @BRANCHES B\nON\t-- B.ORG = A.ORG\t-- User forgot this predicate in the join\n\tB.BRANCH = A.BRANCH;\n<\/pre><\/div>\n\n\nORG<\/strong><\/td>ACCOUNT_ID<\/strong><\/td>BRANCH<\/strong><\/td>BALANCE<\/strong><\/td>BRANCH_NAME<\/strong><\/td>BRANCH_STATE<\/strong><\/td><\/tr>1<\/td> 18<\/td> 60<\/td> 50.00<\/td> Branch of ORG 1<\/td> VIC<\/td><\/tr> 1<\/td> 18<\/td> 60<\/td> 50.00<\/td> Branch of ORG 2<\/td> VIC<\/td><\/tr> 1<\/td> 19<\/td> 60<\/td> 150.00<\/td> Branch of ORG 1<\/td> VIC<\/td><\/tr> 1<\/td> 18<\/td> 60<\/td> 150.00<\/td> Branch of ORG 2<\/td> VIC<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\nPreventing queries like this comes down to user experience and training. If they see duplicates in their data \u2013 their initial thoughts should be \u201cWhy do I have duplicates?\u201d <\/p>\n\n\n\n
Duplicates might be legitimate and a DISINCT might be OK \u2013 but quite often it is because of a bad join. For an inexperience user \u2013 it might be overwhelming to break down a large table to find where the duplicates are coming from, and they might need help from a more experienced user. This is where internal data forums are useful \u2013 where people can help each other with their problems.<\/p>\n\n\n\n
4. The “Ever lengthening” Query<\/h2>\n\n\n\n This is a query I see scheduled daily for users getting trends over time for a BI tool:<\/p>\n\n\n
\nSELECT *\nFROM dbo.A_TABLE A\nJOIN dbo.B_TABLE B\nON\tB.KEY_1 = A.KEY_1\nWHERE\n\t--------------- Start Date ---------------\n\tA.BUSINESS_DATE >= '2025-01-01' AND\n\t------------------------------------------\n\tA.PREDICATE_1 = 'X' AND\n\tB.PREDICATE_2 = 'Y';\n\n<\/pre><\/div>\n\n\nInitially it starts off OK \u2013 running fast and the user is happy. But as time goes by; the query gets slower and slower; returning more and more data. Eventually it gets to the point the query goes off into the nether and never returns. Since it is always starting the from the same point; it is constantly re-querying the same data over an over again<\/p>\n\n\n\n
There are a couple of options you can handle queries like these:<\/p>\n\n\n\n
\nReally examine the requirements of the user’s needs. How much data is really relevant for their trending report? In 2027; will data from 2025 be useful for the observer of the report?<\/li>\n\n\n\n Create a table specific for this report and append a timeframe of data (eg daily) onto it. The report than can just query this specific table and not having to regenerate complex joins over and over again for previous timeframes.<\/li>\n<\/ol>\n\n\n\n<\/p>\n\n\n\n
3. The “Linked Database” Query<\/h2>\n\n\n\n With the growth of our Data Warehouse, other database on different servers started building process flows off our database; sometimes without out knowledge that they are doing this until we see a linked server connection coming up.<\/p>\n\n\n\n
What pattern they use our database is where the problems lie. One downstream database runs this query every day:<\/p>\n\n\n
\nSELECT *\nFROM REMOTE_SERVER.SOME_DATABASE.dbo.A_BIG_HISTORICAL_TABLE;\n<\/pre><\/div>\n\n\nSo, they are truncating a landing table on their database and grabbing the whole table. Hundreds of millions of rows transferring across a slow linked server; taking hours and hours. And with time; this will get slower and slower as the source table grows its history.<\/p>\n\n\n\n
This is a tricky one to tackle with the downstream users. Odds are if they design a cumbersome process like this their appetite for change to a faster (yet more complex solution) might not be high; especially if their process \u2018works\u2019.<\/p>\n\n\n\n
If you can get around the political hurdle and convince them the process must change; there are many options to improve the performance.<\/p>\n\n\n\n
\nUse a ETL tool to transfer the data across. Even a simple python script to copy batch by bath files across will be quicker than using a linked server.<\/li>\n\n\n\n Assess the downstream use cases for the data. Do they need the whole table with the complete history to satisfy their requirements? Do they need all the columns; or can some be trimmed off to reduce the amount of data getting transferred?<\/li>\n\n\n\n If the source table a historical table; only bring across the period deltas. With the downstream process I am having trouble with now; they are bringing across twenty-one million rows daily. If they only bring across the deltas from the night loads it brings across a whopping five thousand rows. That is 0.02% of the total row count. Add in a row count check between the two systems to have confidence that the two tables are in sync and the process will be astronomically quicker.<\/li>\n<\/ol>\n\n\n\n<\/p>\n\n\n\n
2. The \u201cI don\u2019t know where that query comes from\u201d Query.<\/h2>\n\n\n\n We have a continuing problem from a Microsoft Power BI report that runs a query that looks at data from the beginning of the month to the current date. It was a poorly written query and therefore as the month went along its performance got worse and worse.<\/p>\n\n\n\n
Since the username associated with the query was from the Power BI server\u2019s account \u2013 we had no idea who the query belonged to as a couple of simple fixes could drastically improve the performance.<\/p>\n\n\n\n
We contacted the team that manages Power BI server and asked them to find the query and update the sql code. <\/p>\n\n\n\n
They said it was completed, and the report sql was updated.<\/p>\n\n\n\n
But soon the sql was back \u2013 running for hours and hours.<\/p>\n\n\n\n
We contacted the team again \u2013 <\/p>\n\n\n\n
\n\u201cHey that query is back.\u201d<\/p>\n<\/blockquote>\n\n\n\n
They try updating the report again; but soon the query was back again.<\/p>\n\n\n\n
So, either someone was constantly uploading an original copy \u2013 or another copy of the report was buried somewhere on the server that the admins could not find. <\/p>\n\n\n\n
Since we do not have access to their system, it hard to determine what is happening.<\/p>\n\n\n\n
The barbaric solution would be to block the Power BI user from our system; but goodness knows how many critical business processes that will disrupt. <\/p>\n\n\n\n
The best solution at the minute we are doing is just constantly killing the running SQL code on the database with the hope that someone will identify the constantly failing report. This can be tricky as well if the Power BI server automatically tries rerunning failed reports.<\/p>\n\n\n\n
1. The \u201cI don\u2019t listen to your advice\u201d Query.<\/h2>\n\n\n\n Disclaimer \u2013 this is a frustration rant that I have to get off my chest.<\/p>\n\n\n\n
We have a user that constantly runs a long running crook query. As in when I collated all the long running queries in research for this post \u2013 his was at the top by a significant margin.<\/p>\n\n\n\n
The problem with the query itself is a join using an uncommonly used business key in a table that is not indexed. The fix itself is quite simple fix; use the primary keys in join.<\/p>\n\n\n\n
But he has been running the same code for months \u2013 locked our nightly loads several times and caused incidents.<\/p>\n\n\n\n
We tried the carrot approach. \u201cHey here is optimised code. It will make your query run quicker and be less burden on our database.\u201d Got indifferent replies. <\/p>\n\n\n\n
More locked loads we included his manager into correspondence, but she did not seem to want to be involved.<\/p>\n\n\n\n
With my carrot approach communication, I gained the impression that he had a self-righteous personality and thinks his role and report is above question.<\/p>\n\n\n\n
Enough was enough \u2013 it was the stick approach time.<\/p>\n\n\n\n
We raised an operational risk against his activity.<\/p>\n\n\n\n
The Ops risk manager asks, \u201cDo you have evidence that you attempted to reason and help?\u201d<\/p>\n\n\n\n
\u201cYep,\u201d I replied, \u201cHere is a zipped-up folder of dozens of emails and chat messages that I sent him.\u201d<\/p>\n\n\n\n
Senior managers were involved; with my senior manager commenting to my manager in the background \u201cIs that guy all there?\u201d<\/p>\n\n\n\n
Anyway, my manager wrote a very terse email saying to correct their query and if they crash the loads again; they will get their access removed from the database.<\/p>\n\n\n\n
No committal reply. No apology. And to this day they are still running the same query; but just under the radar that it is not locking our loads. <\/p>\n\n\n\n
I am watching him like a hawk \u2013 waiting for the day that I can cut his access from the database.<\/p>\n\n\n\n
It is a pity that our Database does not have a charge back capability of processes used. I bet if his manager got a bill of $$$ from one person running one query; she would be more proactive in this problem. <\/p>\n\n\n\n
In retrospect, I would have campaigned to have his access cut right away and make him justify why he should have it back. When there is hundreds of other people doing the right thing on the database; it is not fair that their deliverables are getting impacted by the indifference attitude of one user.<\/p>\n","protected":false},"excerpt":{"rendered":"
For over fifteen years I have worked with a Microsoft SQL Data Warehouse that is over twenty-five years old. Although it is ancient in this world...<\/p>\n","protected":false},"author":1,"featured_media":1621,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[13,17],"tags":[],"class_list":["post-1613","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-code","category-sql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1613","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1613"}],"version-history":[{"count":12,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1613\/revisions"}],"predecessor-version":[{"id":1629,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1613\/revisions\/1629"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1621"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1613"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1613"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1613"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}