UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Running Out Of Expletives For T-SQL, SQL Server 2008 R2    
 
   
firlandsfarm
post Jun 21 2019, 03:12 AM
Post#1



Posts: 381
Joined: 28-April 02
From: Heathfield, England


Yes I know you all tell me it's a wonderfully powerful language but a language is useless if it cannot communicate between parties (the data and the user in this case) and I thank those with the patience to read and respond to my rants about how 'stupid' the developers of this language were but I'm happy to have someone make the defence for SQL in this instance ...

I have an SQL Server database (2008 R2) where I use Access as the FE end in an attempt to bring some commonsense to SQL. This post concerns SQL's inability to use the AS command. The SQL tables are linked to the Access FE. My MO is to usually build a query in Access, take the SQL code from that, convert it into T_SQL and then either paste that code into the SQL database as a View or use it as a Pass Through query from Access. If I go the SQL View route then I link the view to Access ... it all depends on what I intend to do with the returns of the code.

So for a particular query Access gave me the SQL code ...

CODE
SELECT dbo_NewDataDeDuped.EVENT_DT,
dbo_Percentages.Percentage/100 AS [Percent],
dbo_NewDataDeDuped.BSP,
IIf([BSP]*[Percent]<1.01,1.01,[BSP]*[Percent]) AS E_BackOdds,
([BSP]-1)*[Percent]+1 AS C_BackOdds,
[BSP]/[E_BackOdds] AS E_BackStake,
[BSP]/[C_BackOdds] AS C_BackStake,
dbo_NewDataDeDuped.IPMIN,
Format(IIf([E_BackOdds]>[IPMIN],1,0),0) AS E_XPcentBet,
Format(IIf([C_BackOdds]>[IPMIN],1,0),0) AS C_XPcentBet,
dbo_NewDataDeDuped.WIN_LOSE,
IIf([WIN_LOSE]=1,([BSP]-1),-1) AS BackRet,
IIf([WIN_LOSE]=1,-([BSP]-1),1) AS LayRet,
IIf([E_XPcentBet]=1,IIf([WIN_LOSE]=1,([E_BackOdds]-1)*[E_BackStake],-[E_BackStake]),0) AS E_BackRet,
IIf([C_XPcentBet]=1,IIf([WIN_LOSE]=1,([C_BackOdds]-1)*[C_BackStake],-[C_BackStake]),0) AS C_BackRet,
[LayRet]+[E_BackRet] AS E_NetRet,
[LayRet]+[C_BackRet] AS C_NetRet
FROM dbo_NewDataDeDuped, dbo_Percentages
WHERE (((dbo_NewDataDeDuped.EVENT_DT)>#5/1/2019#) AND ((dbo_NewDataDeDuped.EVENT_NAME)<>"TO BE PLACED"))
ORDER BY dbo_NewDataDeDuped.EVENT_DT, [Percentage]/100, dbo_NewDataDeDuped.BSP;

(I added the c/r's to make the code more easily readable)

This works fine in Access using the linked SQL tables.

I translated this code to ...
CODE
SELECT        
TOP (100) PERCENT dbo.NewDataDeDuped.EVENT_DT,
dbo.Percentages.Percentage / 100 AS [Percent],
dbo.NewDataDeDuped.BSP,
CASE
         WHEN [BSP] * [Percent] < 1.01
         THEN 1.01
         ELSE [BSP] * [Percent]
END AS E_BackOdds,
(dbo.NewDataDeDuped.BSP - 1) * [Percent] + 1 AS C_BackOdds,
dbo.NewDataDeDuped.BSP / E_BackOdds AS E_BackStake,
dbo.NewDataDeDuped.BSP / C_BackOdds AS C_BackStake,
dbo.NewDataDeDuped.IPMIN,
CASE
         WHEN [E_BackOdds] > [IPMIN]
         THEN 1
         ELSE 0
END AS E_XPcentBet,
CASE
         WHEN [C_BackOdds] > [IPMIN]
         THEN 1
         ELSE 0
END AS C_XPcentBet,
dbo.NewDataDeDuped.WIN_LOSE,
CASE
         WHEN [WIN_LOSE] = 1
         THEN [BSP] -1
         ELSE -1
END AS BackRet,
CASE
         WHEN [WIN_LOSE] = 1
         THEN - ([BSP] - 1)
         ELSE 1
END AS LayRet,
CASE
         WHEN [E_XPcentBet] = 1
         THEN
                  CASE
                           WHEN [WIN_LOSE] = 1
                           THEN ([E_BackOdds] - 1) * [E_BackStake]
                           ELSE - [E_BackStake]
                  END
         ELSE 0
END AS E_BackRet,
CASE
         WHEN [C_XPcentBet] = 1
         THEN
                  CASE
                           WHEN [WIN_LOSE] = 1
                           THEN ([C_BackOdds] - 1) * [C_BackStake]
                           ELSE - [C_BackStake]
                  END
         ELSE 0
END AS C_BackRet,
LayRet + E_BackRet AS E_NetRet,
LayRet + C_BackRet AS C_NetRet
FROM            
dbo.NewDataDeDuped CROSS JOIN dbo.Percentages
WHERE        dbo.NewDataDeDuped.EVENT_NAME = 'TO BE PLACED'

I've tried this code with and without [] around the columns, I've tried it with and without the database name when identifying the columns and I've tried changing [Percent] to [Pcent] just in case it gets confused with PERCENT and [Percent] but all to no avail. I believe the translation is correct because I asked SSMS to verify it which it did and I corrected the errors it found but any corrections will be gratefully received. smile.gif (Note: SSMS added the "CROSS JOIN" when I tried to run it but it seems logical as the Percentages tbl only has one field, [Percent], and is included for the query to run with multiple values of [Percent]). My reason for posting is that after correcting the coding errors identified I started to get a whole list of "Invalid Columns" ...

Attached File  InvalidColumns.JPG ( 41.94K )Number of downloads: 0


Why? Do I have to substitute the basic formulae every time? I thought the whole point of using 'AS' is to simplify code. The alias given to each column/expression doesn't seem to break any naming conventions and as I have said ... it works fine in Access pre-translation!


--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
jleach
post Jun 21 2019, 04:53 AM
Post#2


UtterAccess Editor
Posts: 10,039
Joined: 7-December 09
From: St Augustine, FL


>> Do I have to substitute the basic formulae every time? <<

Yea, pretty much.

One technique is to create a "base query" and that generates your Percent column, name it as such, and your "main" query would wrap this base query, thus utilizing the name of the calculated field rather than requiring the expression each time.


--------------------
Go to the top of the page
 
nvogel
post Jun 21 2019, 05:12 AM
Post#3



Posts: 977
Joined: 26-January 14
From: London, UK


You can't reference a column alias in the same part of the query that you define the alias. You have to nest the query for that to work. Like this:

SELECT [BSP] * [Percent]
FROM
(
SELECT Percentage AS [Percent], ...
FROM ...
) x;

If you are trying to write SQL Server queries then use SQL Server Management Studio, don't use the Access Query Designer. Access's version of "SQL" is very different to standard SQL and for anything other than simple queries just isn't likely to work in SQL Server. Added to that, the Query Designer in Access can do some strange things to your code.

It looks like you have also tried to use the View Designer in Management Studio. I suggest you avoid it. View Designer is a bit confusing if you are new to SQL and in the long run it's much better to get used to editing your queries in the editing window. By writing queries yourself you will learn how to write and understand SQL and you'll have access to the full range of features which are not all available through the designer UI.

Incidentally, SQL Server 2008 R2 drops out of its extended support period next month, which could leave you vulnerable to security flaws and other bugs in future. I suggest you consider upgrading to a newer version very soon.


This post has been edited by nvogel: Jun 21 2019, 05:12 AM
Go to the top of the page
 
firlandsfarm
post Jun 21 2019, 07:19 AM
Post#4



Posts: 381
Joined: 28-April 02
From: Heathfield, England


JL
Thanks but as displayed in the screen grab I included it's not just the "Percent" alias ... it's all aliases, 10 of them! frown.gif And I'm sorry but I don't know what you mean by a "base query", I've tried a search for it as a technique but cannot find an article explaining the technique. frown.gif Are you saying to have a pre-query to set the values of each alias and then have a follow-on query to manipulate them in the formulae?

NVogel
Thanks for your comments but I'm afraid I'm also not really following your example of nesting. frown.gif I don't see what you are suggesting I need to do and do I need to play around like that for each of the 10 aliases I am using and for each use of each alias? I can't see how that is simplifying the code! What is the point of an alias if you can't use it?

BTW I appreciate Access SQL is different to T-SQL, the bulk of my post revolved around that. That's why I posted the Access SQL code and the T-SQL code that I translated from it. As I said I use Access to give me the structure and then translate into T-SQL and have found this to be totally usable and easy for me to work with. I don't find SSMS any help in building queries as Access is. For me, it does not compare in even the slightest way with the Access Query Designer. And I'm sorry for my ignorance but I have no idea what you are distinguishing as the "View Designer" in Management Studio and "the editing window".

Thanks for reminding me of the withdrawal of support for 2008 R2 but it is not in my control. I run a proprietary app on my computer that installed 2008 R2 as part of it's installation. The app provides the main database and automatically downloads and populates daily updates to it. My data is additional data that I collect on the same server but in a separate database as complimentary to the app's database. I have asked the supplier of the app if they intend to upgrade from 2008 R2 but they have no plans so to do.


--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Daniel_Stokley
post Jun 21 2019, 08:02 AM
Post#5



Posts: 321
Joined: 22-December 14
From: Grand Junction, CO, USA


Hello, and please PMFJI. Here is what I notice.

First; your query does a calculation and calls the result [Percent]: dbo.Percentages.Percentage / 100 AS [Percent].

That is OK except that Percent is a reserved word. I recommend you choose a different alias. Lets say NewDataPercent.

Second; your query attempts to use that alias in a later calculation:

SQL
CASE
WHEN [BSP] * [Percent] < 1.01
THEN 1.01
ELSE [BSP] * [Percent]
END AS E_BackOdds


That is not allowed. That is what nvogel and jleach are telling you.

I recommend you break this up into at least two queries. The first query would select all the data elements and the calculation for NewDataPercent. These results would be stored in a temp table.

The second query would select everything from the first query and also perform the other calculations that depend on NewDataPercent.


Go to the top of the page
 
Daniel_Stokley
post Jun 21 2019, 08:18 AM
Post#6



Posts: 321
Joined: 22-December 14
From: Grand Junction, CO, USA


On the other hand, since you are not choosing any other data elements from dbo.Percentages, you could prepare a temp table based on dbo.Percentages. For example:
SQL
IF OBJECT_ID('tempdb..#Percentages') IS NOT NULL drop table #Percentages;
SELECT 0.01 as [PercentageVal] INTO #Percentages -- create the table and insert one value
insert #Percentages ([PercentageVal]) values -- add multiple values with one statement
(.02),(.03),(.04),(.05), (.1),(.15),(.2),(.25), (.3),(.35),(.4),(.45), (.5),(.6),(.7), (.75),(.8),(.9),(.99)

IF OBJECT_ID('tempdb..#PercentagesX100') IS NOT NULL drop table #PercentagesX100;
SELECT [PercentageVal] * 100 [PercentageVal100] INTO #PercentagesX100 FROM #Percentages;

SELECT * FROM #PercentagesX100;


NOTE: both "Percent" and "Percentage" are reserved words.
This post has been edited by Daniel_Stokley: Jun 21 2019, 08:21 AM
Go to the top of the page
 
GroverParkGeorge
post Jun 21 2019, 08:49 AM
Post#7


UA Admin
Posts: 35,520
Joined: 20-June 02
From: Newcastle, WA


"I have asked the supplier of the app if they intend to upgrade from 2008 R2 but they have no plans so to do."

And have they offered updates to their base application?

It's hard to picture a commercially successful product that doesn't, but if they are stuck on this version of SQL Server, I have to wonder how they feel about other elements of their product offerings. Having no competitors in this space, perhaps, might explain that.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cheekybuddha
post Jun 21 2019, 09:09 AM
Post#8


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Since you are building calculations upon calculations you will probably need several base queries.

Here's an attempt:
CODE
SELECT
  b4.EVENT_DT,
  b4.[Percent],
  b4.BSP,
  b4.E_BackOdds,
  b4.C_BackOdds,
  b4.E_BackStake,
  b4.C_BackStake,
  b4.IPMIN,
  b4.E_XPcentBet,
  b4.C_XPcentBet,
  b4.WIN_LOSE,
  b4.BackRet,
  b4.LayRet,
  b4.E_BackRet,
  b4.C_BackRet,
  b4.LayRet + b4.E_BackRet AS E_NetRet,
  b4.LayRet + b4.C_BackRet AS C_NetRet
FROM (
  SELECT
    b3.*,
    CASE
      WHEN b3.E_XPcentBet = 1 THEN
        CASE
          WHEN b3.WIN_LOSE = 1 THEN (b3.E_BackOdds - 1) * b3.E_BackStake
          ELSE -b3.E_BackStake
        END
      ELSE 0
    END AS E_BackRet,
    CASE
      WHEN b3.C_XPcentBet = 1 THEN
        CASE
          WHEN b3.WIN_LOSE = 1 THEN (b3.C_BackOdds - 1) * b3.C_BackStake
          ELSE - b3.C_BackStake
        END
      ELSE 0
    END AS C_BackRet
  FROM (
    SELECT
      b2.*,
      b2.BSP / b2.E_BackOdds AS E_BackStake,
      b2.BSP / b2.C_BackOdds AS C_BackStake,
      CASE
        WHEN b2.E_BackOdds > b2.IPMIN THEN 1
        ELSE 0
      END AS E_XPcentBet,
      CASE
        WHEN b2.C_BackOdds > b2.IPMIN THEN 1
        ELSE 0
      END AS C_XPcentBet
    FROM (
      SELECT
        b1.*,
        CASE
          WHEN b1.BSP * b1.[Percent] < 1.01 THEN 1.01
          ELSE b1.BSP * b1.[Percent]
        END AS E_BackOdds,
        (b1.BSP - 1) * b1.[Percent] + 1 AS C_BackOdds
      FROM (
        SELECT        
          d.EVENT_DT,
          p.Percentage / 100 AS [Percent],
          d.BSP,
          d.IPMIN,
          d.WIN_LOSE,
          CASE
            WHEN [WIN_LOSE] = 1 THEN [BSP] - 1
            ELSE -1
          END AS BackRet,
          CASE
            WHEN [WIN_LOSE] = 1 THEN -([BSP] - 1)
            ELSE 1
          END AS LayRet
        FROM dbo.NewDataDeDuped d
        CROSS JOIN dbo.Percentages p
        WHERE d.EVENT_NAME = 'TO BE PLACED'
      ) AS b1
    ) AS b2
  ) AS b3
) AS b4
;

--------------------


Regards,

David Marten
Go to the top of the page
 
firlandsfarm
post Jul 2 2019, 06:14 AM
Post#9



Posts: 381
Joined: 28-April 02
From: Heathfield, England


George ... they have added the odd additional facility in the past, maybe 1 or 2 per year and they will probably continue to do this going forward but I am directed by them because there is no competitive product that I am aware of. They issues daily updates to the data. I guess it's a matter of staying with it and seeing if they upgrade or until it ceases to work!

Daniel ... "... Percent is a reserved word. I recommend you choose a different alias." As I said "I've tried changing [Percent] to [Pcent] ... but all to no avail." frown.gif

"That is what nvogel and jleach are telling you." ... the oft repeated conundrum! I'm sure that is what they were trying to tell me but if i didn't understand what they were saying as explained in my follow-on response then I wouldn't have known that was what they were trying to tell me! smile.gif I did suggest in my response that I had sussed they were saying what I think you have now confirmed, thanks.

And I'm sorry Daniel but your code is Double Dutch to me! I don't understand a single step of it ... if the code in your second post is simply to replace mine referencing "dbo.Percentages.Percentage / 100 AS [Percent]" then it seems a little extravagant (for a newbie). If not how do I use your code?

And finally Cheeky ... wow, you clearly put a lot of effort into that for which I am very grateful but it will take me years to follow all that code frown.gif I'm not understanding where all the b1's, b2's etc. are coming from nor for! frown.gif And remember I've still not had clarification what a "base query" is ... as I said I cannot find any reference to it anywhere on the Internet. I'm assuming you have nested several queries into one set of code ... that's a subject I have not tried to grasp yet.

Guys, I don't want to sound ungrateful and unreasonable but when a question is posted here it because the questioner doesn't understand something so when you respond to them please try and tone down the technicality of your reply otherwise, by definition, your answer runs the risk of not being understood! My reference to "base query" earlier in this reply is a case in point.

I think I have understood that the problem is because I cannot use an alias as a value in a calculation in the same query that the alias is defined, yes? OK, if that is the case I can resolve that. Cheeky, if my understanding of how you have structured your code is correct then that is a design strategy I will pick up in the future but now is possibly too soon for my learning curve ... I'm not yet at the stage where I can absorb and understand such long code sets. frown.gif

Anyway thanks to all. I'll not be back unless something doesn't work with the stacking of queries. I've always thought that to keep it all in one query would mean faster extraction (I am talking of millions of records here) but maybe I need to rethink along the lines of 'one thing at a time' stacking of queries. smile.gif

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Minty
post Jul 2 2019, 06:47 AM
Post#10



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


I'll try a differently worded explanation.

In T-SQL when you use an alias it is only available if you then query the query that the alias was created in.
Access also does this sometimes particularly if you use aggregate functions. Because it doesn't always do it a lot of people get caught out by this behaviour.

An alias helps when referencing long table names, or to make things more readable, and in your case, can only do that if you set up the alias first as others have suggested in a underlying query (Sub Query or Base Query)

CheekyBuddas excellent example is using table alias's to identify references to the same table multiple times.

Hopefully all as clear as mud ?
Go to the top of the page
 
cheekybuddha
post Jul 2 2019, 06:47 AM
Post#11


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


What happens if you chuck the SQL I posted into SSMS and execute it?

--------------------


Regards,

David Marten
Go to the top of the page
 
firlandsfarm
post Jul 2 2019, 10:11 PM
Post#12



Posts: 381
Joined: 28-April 02
From: Heathfield, England


Minty ... thanks for that, it is as I suspected in my last post, there is no problem with that. smile.gif I have found that Access seems to object if you try and sort an alias in an aggregate environment. Take away the sort and it often solves the problem. And you say "In T-SQL when you use an alias it is only available if you then query the query that the alias was created in." but then 3 lines later say "CheekyBuddas excellent example is using table alias's to identify references to the same table multiple times" OK, so you can't use an alias unless it's an alias you can use! iconfused.gif and to think some say that French irregular verbs are illogical and confusing, clearly they've never come across SQL.

Cheeky ... I tried your code yesterday and yes, it produced a result. smile.gif I haven't checked it for accuracy yet because although it started at a sprint it seemed to get slower and slower as the query went on! Eventually after probably around 6 hours (I didn't see the need to time it when I started and forgot that SSMS has a query timer in the display) I gave up and closed it down. It had processed some 7 million records by then but it was very slow at the end with the record count only jumping by one or two hundred at a time ... it did seem to slow down as it progressed which seems a little strange unless maybe because of time spent snuffling the results as the list gets longer. frown.gif But my main interest in the post was not that it worked but why/how it worked as part of my learning process. That's the only way for me to stop troubling you guys. smile.gif Anyway I'm going to re-run with some sample data to check the accuracy later today.

BTW, while I have you here ... how do you just simply edit the contents of a solitary field/record in SSMS ... surely you don't have to write code do you!


--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
Minty
post Jul 3 2019, 09:26 AM
Post#13



Posts: 312
Joined: 5-July 16
From: UK - Wiltshire


Just to clarify (if I can explain it) a table alias doesn't do anything other than refer to a table, no calculations or other indirect references involved. A field alias however does do that.

To your SSMS question, if you want to edit the values in a table directly a simple right click of the table and select the Edit Top 200 rows option will get you into it.
You can then use the criteria pane to filter down to the records you want to edit.
Go to the top of the page
 
MadPiet
post Jul 3 2019, 12:00 PM
Post#14



Posts: 3,220
Joined: 27-February 09



QUOTE
BTW, while I have you here ... how do you just simply edit the contents of a solitary field/record in SSMS ... surely you don't have to write code do you!

You mean write a SQL statement to do it? how else is it supposed to happen? That's the tradeoff in SQL Server - you have more control, but it means you have to do a lot more work yourself. You can use Access as a front end to SQL Server and Access handles all the data manipulation under the covers. If you're dealing with SQL Server directly, you do all of that.
Go to the top of the page
 
firlandsfarm
post Jul 4 2019, 12:34 AM
Post#15



Posts: 381
Joined: 28-April 02
From: Heathfield, England


OK Minty, thanks, got both explanations ... one is a change of name while the other is a calculated variable, fair enough and I have never used the "Edit Top 200 rows" before mainly because it seemed insignificant as many of my tables have from 1 million to 6 million records so only ever seen it as just an opportunity to see what the data looks like and nothing more.

MadPiet, thanks for your reply, I use Access as my FE but when working in SSMS and noticing the need to correct a typo in one field in one record of a 6 million record table having to open the Access FE, click on the linked table, find the one record and change the one field seemed to be a very inefficient waste of time and writing a line of Update code in SSMS just to achieve the same correction seemed equally an inefficient waste of time. I understand what you say about the glories of SQL but I'm not here for the glories, I'm here just because my DB is over 2Gb and I find SQL/SSMS the most user-unfriendly, time wasting language anyone has ever invented. I prefer Minty's solution though it's still a case of getting from A to B but being forced to go via C and D to compensate for SQL/SSMS's shortcomings! SSMS should be the SQL FE and giving the ability to change the contents of a single field in a single record would not have been that difficult for them to offer.

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
cheekybuddha
post Jul 4 2019, 02:51 AM
Post#16


UtterAccess VIP
Posts: 11,463
Joined: 6-December 03
From: Telegraph Hill


Hi,

Ok you original post you wrote:
QUOTE
So for a particular query Access gave me the SQL code ...


In the query you use a CROSS JOIN. This is a Cartesian join and if you have several million records per table being joined you will be creating a result of the product of the number of rows in each table.

EG if dbo.NewDataDeDuped has 7 million rows and dbo.Percentages has 6 million rows, you will be trying to extract a recordset from 42 million records. It really will take a long time.

What is your original query trying to do? Are you sure you want a cross join here?

Also, are these tables ones that you have created in your own db, or are they from your other software's database?

--------------------


Regards,

David Marten
Go to the top of the page
 
jleach
post Jul 4 2019, 05:55 AM
Post#17


UtterAccess Editor
Posts: 10,039
Joined: 7-December 09
From: St Augustine, FL


>> SSMS should be the SQL FE and giving the ability to change the contents of a single field in a single record would not have been that difficult for them to offer. <<

I can imagine a great many DBAs cringing at the thought!

I guess it's one of those things that you get used to and eventually grows on you. I despise the Access visual query editor and haven't used it in years, much preferring to write everything by hand. Once you really "get it", it's far easier and more efficient (yes, even for complex queries - in fact, the QBE in Access can't even handle complex queries... after about medium-ish complexity, it's simply not possible to do any more without writing in raw SQL).

Once you're fluent with it, writing an update statement to change a single value takes about two seconds.

I understand your frustration: despite my own love of working with raw SQL, it's hardly everyone's basket of berries, and as Access developers we really tend to wear many hats and want to "just get it done" (whereas MSSQL/SSMS is more or a tool for people who spend most of their day in that shell). Alas, if you outgrow an Access database and have to move up some, you have to take what comes with it also.

I hadn't touched SQL Server in my first 10 years or so programming Access, and was very apprehensive about the change. I think it took me a few months to become one of those "why would I ever use anything else!" people.


>> This is a Cartesian join and if you have several million records per table being joined you will be creating a result of the product <<

As a side note, often times we'll use the technique of pulling the subsets from the main tables, dumping them into a pair of temp tables, then running the cross join on those temp tables (so the CROSS is working only against, say, 100k records instead of 10m). Sometimes you can get away with writing your queries correctly to do so (check your execution plans) without the use of temp tables, but in MSSQL, temp tables can be created on the fly and dropped as soon as you're done with them, making it a relatively trivial tasks in many cases (and in this starts to come some of the power that JET/ACE can't really hint at).


--------------------
Go to the top of the page
 
firlandsfarm
post Jul 4 2019, 06:56 AM
Post#18



Posts: 381
Joined: 28-April 02
From: Heathfield, England


Hi Cheeky and thanks for your continued interest ... Yep I know the numbers and yep I intended a CROSS JOIN. I was looking to do an "all in one" rather than repeat run the view individually for each of the Percentage values. I expected it to take a long time but not that long (and more to finish it). Usually to query the 6 mill table (it's actually 6.9) takes a putupable amount of time so I was thinking this will take 7 times putupable minutes, I can live with that. I wasn't expecting it to get slower and slower as the number of rows processed grew. Using a very crude test with phone stopwatch and the SSMS record counter the first 100k record count takes 14 seconds, the second 100k takes 19 seconds and the third 100k takes 24 seconds ... it may be crude but it shows a trend.

I was going to start a new thread to ask/discuss the design implications of large table databases but maybe as you have moved that way in your questions I will post here in my response ...

Are there any design principles that need special consideration with large table databases that can/are usually ignored with smaller databases because their shortcomings are not noticed ... who cares if a query takes 0.01 of a second or 1 second but they would care a lot if it took 1 hour instead of 36 seconds! This table is by far the largest I use and I was genuinely surprised how long the query was taking to run because other interrogations took just a minute or two. So I'm starting to ask myself ...

The 'table' is made up of data from 5 countries (the largest being Australia at 2.8 million records and the smallest RSA with 0.3 million). The countries are mutually exclusive, I cannot foresee a reason when I would want to combine data from two or more countries and if I did I could always do a UNION query. So it could be redesigned into 5 tables, one for each country. Would that make queries run faster because there would be no need to filter the requested country first?

But I would then need to have 5 queries for each query (and I already find myself getting confused over what/how each query does with sub/base queries thrown into the list). Is there a way to have one query and somehow direct it to the required country table. Something like ... run query ... [Which Country?] ... enter Country ... Australia ... query uses Australia table. I suppose I'm asking can you put a variable into code to request a value and call a selected table?

If not I will potentially need to increase my number of queries by a factor of 5 ... is there a well tried and tested way to keep track of a large number of queries. Should I have some form of table listing the queries and their inter-relationships or is there a graphics package that can set out the various relationships between the queries?

I realise that with smaller databases there is a reluctance to store calculated values and understand why but ... when you have very large tables and are often performing the same calculation is there not an argument to calculate once and store if the base values are not changing i.e. what happened in the past happened in the past and is unlikely to change unless there was an error so calculate what you need and store it to save continually multiplying 2 x 2 every time you need the answer. What does Tesco or Walmart do with their customer purchases details? Do they keep every item stored individually and everytime they want investigate orders totally less than X they go through and re-calculate the total for every order or is the total calculated on data entry and stored?

There seems to be a strong case for running the code once and creating a new table that is then topped up as required (the data is for spotting trends and so does not need to be live, weekly or maybe even monthly would be acceptable.

What about indexes? Again you can get away with not indexing with small tables but maybe the larger the table the more indexes should be used.

Looking at the above maybe I should have started a new thread on the subject of any special considerations for large table databases! (Dear Moderator, please feel free to move this if you wish to.)

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
firlandsfarm
post Jul 4 2019, 07:24 AM
Post#19



Posts: 381
Joined: 28-April 02
From: Heathfield, England


jleach I think you have summed up my feelings towards SQL just about so. smile.gif I have toyed with the thought of breaking my data into sub 2Gb Access databases and then linking the tables into a central Access DB ... that's how frustrated (desperate) I feel sometimes! My biggest gripe is why the need for two different SQL languages from the same producer? It just doesn't make any sense to me not only because of the frustration in switching when you have to but also the time spent creating and developing two languages!

As I said in my last post (No. 18) I understand the numbers, I was caught out by the time and in particular the deteriorating processing speed ... I just expected if it takes T seconds to do this for 1 million then it will take 6.9 x T to do it for 6.9 million records ... wrong!

My point of running this query was simple to check the results (remember I was thinking it would finish in a reasonable amount of time). Once checked I would then re-run it to create a table of the values for further investigation ... I could have broken it down into subsets but at the end of the day I need the calculated values for all records so why not do it in one go ... I didn't realise I should go on holiday while it was running! smile.gif

The good thing though is that it has caused me to think a little more about DB design but I'm still after a holy grail ... there is a similar DB on the Internet (MYSQL) and when you query it through the website it tells you how long it has taken to do the job which is usually in fractions of a second. That's what impressed me to try and get to grips with SQL.
This post has been edited by firlandsfarm: Jul 4 2019, 07:26 AM

--------------------
Never doubt the courage of the French - they discovered that snails are edible!
Go to the top of the page
 
GroverParkGeorge
post Jul 4 2019, 09:06 AM
Post#20


UA Admin
Posts: 35,520
Joined: 20-June 02
From: Newcastle, WA


Why are there two Microsoft versions of SQL? Interesting question.

I guess you'd also have to question why Oracle, MySQL, PostGres, et al also have their own versions of SQL. And now there are No-SQL options as well! And why there are multiple programming languages, C#, javascript, VB and on and on? And why we need both Web and Desktop interfaces? And why are there both Mac and Windows computers? And on and on. You name the product and there are variations and alternatives. It does make it harder to "master" all of the potential tools available to us, doesn't it? In fact, now that I think about it, why do soft drink vendors offer both Diet and Regular versions of their products? Or automobile manufacturers offer sedans, SUVs and even pickup trucks, all under the same manufacturing nameplate? Again, it makes for a more varied set of choices--and therefore endless decisions. But is it really all that bad to have diversity in software tools? I suppose it just comes down to one's point of view on such matters.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    22nd August 2019 - 09:24 PM