@Zikato Operating at a F/LOSS maybe? I think it's great that they've completely open sourced the "secret sauce" - under the Affero GPL - so that other cloud providers can't swoop (get the vulture metaphor here? :-) ) in and gorge themselves off any of the dev effort. I think more F/LOSS should move to this model to halt AWS, GCP and Azure (and anyone else) from sitting back and shamelessly profiting from the efforts of others without putting anything back into the community!
@ypercubeᵀᴹ - I think Redis is a case in point - AIUI, they've struggled recently but (again AIUI) all of the major cloud providers have some sort of Redis offering - but aren't giving back - making the company viable - stuff like that!
The Affero GPL means that it can't be hosted by third parties - you are welcome to use the software internally, but you can't turn it into a cloud service!
@Vérace I think it means that if you modify the code and offer it anywhere (for money included, in the cloud included), then the users should have access to the modified source code.
so, in essence, it's not really free, from a certain point of view
Is the GPL free? I don't want to go down that rabbit-hole - what concerns me is cloud giants hosting software and not giving back. I just reread the AGPL - yes, AWS/anyone can host it, but any changes must be made available to the community and not (as I stated earlier) a ban on hosting or placing restrictions on hosting - other than the obligation to make any changes available under the AGPL! Nothing to stop AWS hosting Citus.
However, I believe that many F/LOSS projects are putting clauses that are even more restrictive with a view to closing off "piggy-backing" in any form on software for which the source is available - and I think that's fair enough - if the source is available for me to use, great, that I can't make money off it - that's fine too - and only fair IMHO!
I have two table (Orders, Payments)
they are 1 to many relationships (1:N)
One order can have many payments
Table 1: Orders
id
total_price
1
1000
2
2000
3
3000
Table 2: Payments
order_id(FK of Orders)
amount_payed
1
500
2
2000
3
1000
3
500
3
750
some orders are...
I tried running a performance test. Now, I'm not a SQL Server man, but I "translated" our queries into PostgreSQL and, your query (under PG) appears to get (disastrously) slower and slower as the number of orders increases. I ran these on my home machine with 10,000 orders and 100 payments per order and mine took 158ms and yours took (wait for it...) 05:39.7 (340kms!).
I can't run numbers like this on the fiddle (times out), but if you're game, if you send me some code which will emulate the GENERATE_SERIES(...) part of my code in SQL Server and the relevant INSERTs - I'll run it (groan... install SQL Server...) just to see if it copes better with optimising the code than PostgreSQL... I upvoted your answer anyway because it gave me an insight into LATERAL - something I'm looking at at the minute.
I'm also going to include your LEFT JOIN and COALESCE/ISNULL suggestion - thanks for those! Have to run now - but will update my answer later...
Sorry about the confusion - also, I hadn't indexed that field in my home test - after indexing it runs in ~ 1s = 6x the non-LATERAL query! I should have been warned by the extreme value - but still, I'd like to get to the bottom whether this happens in SQL Server also.
Can you provide the CLUSTERED syntax? Have to run - will be AFK for ~ 3 hrs - I'll get back to this later...
@Vérace I believe that under GPL2 licence, you are totally free to run the program and modify it. The only restriction is if you distribute the modified program in which case you have to distribute the modified source code too. So there is no restrictions on running the program modified in your machines (or your "cloud" machines) ands sell these services.
I had "fun" once with MySQL performance tuning and partial replication over a very narrow bandwidth line. The setup was a combo of mysql and SQL Server and queries were produced by EF.
If EF queries are a torture to SQL Server optimizer, you can imagine what they do to MySQL's one.
@ypercubeᵀᴹ That is exactly my understanding of the GPL2 - and it has led to some cloud vendors "piggy-backing" because there is no restriction on cloud hosting of an app - cloud is not considered distribution under the GPL2 - cloud is considered distribution under the AGPL. I think we agree on this - where I was wrong was that I thought that cloud distribution was flat-out verboten - it is not, but redistribution of source changes (if any) is mandatory! I think we now agree on this also?
I bought a pair of Cambridge Audio wireless headphones from Amazon a couple of days ago, and now Amazon is recommending I buy every pair by every manufacturer like I need so many of them.
@ErikDarling I would create a clustered index if someone would provide me with the code to do this in SQL Server...
WITH cte1 AS ( SELECT o * 100000 AS oid, p * 100 AS amt FROM GENERATE_SERIES(1, 10000) AS o, GENERATE_SERIES(1, 20) AS p ), cte2 AS ( INSERT INTO _order (SELECT DISTINCT oid, 10000000 FROM cte1) ), cte3 AS ( INSERT INTO payment (SELECT oid, amt FROM cte1) RETURNING * ) SELECT * FROM cte3 LIMIT 5;
without it, I can't simulate a reasonable number of records to do any sort of realistic test!
> Microsoft announced today that they’re rebranding learn.Microsoft.com to compileAndRun.Microsoft.com because they found that most users don’t want to learn, they just want to get it to compile and run.
@ypercubeᵀᴹ Thanks - a quick search found that - I was just wondering if there were any added issues - it seems to change the plan! It appears to change the plan for the "ordinary" query also!
I don't mind developers using formated text (xml, json, csv...) at all. I don't even mind them supplying it to, or requesting it from, the database. What I do find offensive is querying (or changing) bits of it inside the database.
Again I think ORMs have a place (the developer side in me speaking I guess). Right tool for the right job, but not always the solution. But they do make developer's lives easier more times than not, so a net win in theory.
Well when they misbehave, consider it extra job security for the DBAs / DB developers. At least it has become a recurring thing for me to work on lol. Win-win-win, in theory.
If you're working on a very small project, with a tight budget and timescale, I dare say coding using an ORM is very attractive. You can always hire Erik to fix some performance issues with your local book club website.
If, on the other hand, you want to create a database useful to more than one data consumer, able to change and scale with updated needs, it's a false economy.
A fairly common method of creating a series in T-SQL consists of using a CTE as the source, something like:
;WITH t AS
(
SELECT n = v2.n * 10 + v1.n
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9...
I'm half joking. Something like EF is no doubt of practical use in some cases. People do tend to adopt a tool and then use it for everything, regardless of the specific circumstances.
I am not interested in working on databases created by, and accessed through, EF though.
@J.D. If you take me saying it is counterproductive as agreement? There are plenty of things good consultants could be used for. Hacking around terrible dynamic SQL and EF code gen in general is not a good use IMO.
@JoshDarnell Probably a more sensible default, but why does it always have to be an on/off choice? Indexing FKs makes sense sometimes.
I've been working on a project that doesn't use an ORM for a while now. I love how straightforward and predictable it is. I don't love some of the tedium that goes along with it.
Of course, most of my work projects with EF are the opposite. I love that I can read from a table and have the results mapped into C# objects easily. But if I have to write a complicated query, or design relationship between tables that's not one of the obviously supported / implemented ones, then there's a lot of thrashing around and time wasting.
@Vérace Yes, the obvious thing to do would be either a clustered index over the foreign key (not as silly as it sounds, the PK doesn't need to be clustered) dbfiddle.uk/AUqQ5S_R or INCLUDE columns on a non-clustered index dbfiddle.uk/WkDQhe3Q
@PaulWhite I don't disagree with most of what you've said so far. But I empathize the developer's perspective too. Databases is not something usually taught or taught well when going to programmer school. And it's definitely a different mindset to solve problems relationally when you've been taught to think iteratively in your schooling.
So in a career dominated (by volume) with developers who weren't properly taught databases, and generally consists of companies who don't spare the time for self learning / growth enough, an ORM is an attractive solution to a problem they don't realize they don't fully understand.
I tend to look at EF as something useful for small databases that are primarily concerned with doing very very basic CRUD. As a rule I usually use Dapper, which means I get to write my own queries, optimize them how I like, and Dapper will map the results automatically.
My experience has been that developers work best when they work on code development. Database designers work best when they design databases. Database developers work best when they implement and maintain a design. DBAs check databases for errors and make backups.
All you need is a stable and sensible database API, then everyone can go around automating and tooling whatever they like.
@PaulWhite True true. Unfortunately there's a lot of companies out there that can't or won't hire full diverse teams of that nature. They leave most hats to be worn by the developers. I guess again kind of a culture thing.
But yes, my company actively encourages self-learning. My boss is happy for me to experiment with anything that will improve my code, and I both develop and manage the apps I create, so I'm holding both ends.
@Charlieface What's your preference on using Dapper and writing queries in the application / API layer as opposed to using a full ORM and writing SQL in procedures and views in the database layer?
It's not necessarily a bad thing if developers wear a few hats. They just need to be prepared to learn a lot. Some developers can't be bothered,a nd just stick to what they know.
@J.D. Depends on whether the app is the only thing accessing the DB. If so I tend to stick it all in the app code, unless it's really big and complex. If there are other apps or processes working on the DB then I tend to put stuff in procedures. But I use Dapper either way, I avoid full ORMs like the plague.
Dapper works perfectly well on results from a sproc, and EF can also use them, so the question of which to use is mostly orthogonal to whether to use procedures or not.
@J.D. Procs do have some benefits over batches re parameter sniffing, temp table caching etc, so I do use them when I see the need. Batches can be kept together with app code, so it's easier to reason about what it's doing. Mix and match basically.
@Charlieface No doubt, interesting perspective. I used Dapper in a previous job but was not a fan. I think it was more so the disorganized implementation of the repository pattern my "team" did (more or less each developer following their own different standards), not really Dapper's fault.
@J.D. I guess you need one or two developers to be the ones who are the "SQL experts". That's how we do it, that means we can enforce some standards. Same goes for most other tech: there are one or two experts who can say how it should be done.
@HannahVernon Which corner of the globe? It's bright sunshine here, surprisingly enough.
@Charlieface Yea no doubt. That's been me everywhere I've worked so far. I'm a developer by trade, self-taught (and still learning :) on the SQL side. Unfortunately I've found that there's even not enough of those guys going around.
PW - nothing stellar that I can see in this release - apart from making (your absolute fave feature! :-) ) regexes more compatible with Oracle - and more sophisticated - it was one area where PostgreSQL was lagging! I'm hoping that by 2025, we'll have a decent storage engine API, Vérace (duly consecrated bishop of PostgreSQL)!
Updated in response to comments.
I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.
CREATE TABLE HashTable(
RecordID VARCHAR(255),
GroupIdentifier VARCHAR(255),
Hash VARCHAR (255),
GroupHashList VARCHA...
@ErikDarling Yip - done that - seems to change the plan - from a table scan to clustered index seek - can't hurt, but it also changes the plan of the non-LATERAL query - I need a decent amout of records to check it out - I'm on the bus at the moment.
I'm not downloading SQL Server 2022 for Linux Developer Edition on Dublin Bus' wifi! ;-)
I created the table, and the index clustered - see fiddle - ran the SET STATISTICS PROFILE = ON; and then dropped the index and ran the query again - plan changes!
I can't upload images anymore: on the Ask Question page, the image upload dialog shows a red error message
Failed to upload image; an error occurred on the server
See the screenshot below (uploaded via imgur's own website):
The response from the server is
{"Success":false,"Error":5,"ErrorMessage"
I also thought it was funny that Erik points out the MAX - then they say that's not true and to watch the video, so he points out the video is the same. They agree and then disagree.
@SeanGallardy one of our main applications is being moved from DB2 to MongoDB because document databases are easier to maintain and leverage for change.
@HannahVernon Boo - unfair - I'm not allowed to reply to my own messages - I actually think that we plebs should be allowed to - for clarifications and the like!
@HannahVernon Meaning you have no idea what the schema is basically? The amount of times I get given a REST API spec where the docs don't match what's returned. Or where you run into the "either single object or array problem" and it's entirely undocumented....
@HannahVernon I'm using Kusto every day now and I absolutely loathe these systems. Hello "tables" with 1000 columns that each dev just does whatever they want with it... such fast development, who needs to think about anything!
@HannahVernon It wasn't an answer though, it was just a guess. I was responding to Conor by saying it "looks" like a bug. If anyone has any further info they are welcome to write a proper answer....
For the absolute love of small fluffy bunnies, please write answers as answers. Even if you're not sure it's the answer. Use comments for asking for clarifications by all means. Do not attach an answer to such comments. I am pleading with you now.
@PaulWhite Plead all you like, but if it's a blind stab in the dark then it probably shouldn't be an answer. It's basically a "do you think it's this maybe? have you tried this?"
> Comments should only be used for asking for clarification, or to leave constructive criticism that guides the author in improving the post, or to add relevant but minor or transient information to a post (e.g. a link to a related question, or an alert to the author that the question has been updated), or to provide site usage guidance. See the help for details.
I have that in a copy-pasta file for just such occasions lol.
in other words, saying "this might be the cause of your problem" is an answer not a comment
UPDATE HT1
SET GroupHashList = C.HashList
OUTPUT inserted.*
FROM HashTable AS HT1
JOIN
(
SELECT
GroupIdentifier,
HashList =
STRING_AGG(HT2.[Hash], ';')
WITHIN GROUP (ORDER BY HT2.[Hash] ASC)
FROM HashTable AS HT2
GROUP BY
HT2.GroupIdentifier
) AS C
ON C.GroupIdentifier = HT1.GroupIdentifier;
That should help you identify the issue, and it follows Conor's advice
@PaulWhite Same result dbfiddle.uk/DzArse6r not sure how to identify the issue from that. Conor's point doesn't seem to be relevant: the inner HashTable reference would never be bound as it's a different scope, so that doesn't make a difference.
Sorry I must be dense. Is that a bug in the optimizer? Or are you saying it's just a binding problem (user error). Because changing the references makes no difference. CROSS APPLY does seem to make a difference though dbfiddle.uk/GKDcI1me unless you use a GROUP BY ()dbfiddle.uk/AZlTrndN
@PW - re. not being excited about MERGE - there was UPSERT which was similar just with different syntax AFAICS... there are a few goodies - as I said, solid but not stellar!
Why wouldn't they see it? There's a huge banner at the top saying it's been migrated, and if you click on the answer from elsewhere it redirects automatically.
@Verace We also have the UNIQUE NULLS NOT DISTINCT feature. And "foreign key ON DELETE SET actions to affect only specified columns". And that's it, for SQL language improvements. On the other hand, SQL Server 2022 adds WINDOW clause and IS (NOT) DISTINCT FROM syntax, support for IGNORE / RESPECT NULLS for FIRST/LAST_VALUE() functions, several new aggregate, JSON, bit and general SQL functions (eg GREATEST()) and of course GENERATE_SERIES().
Id
Codes
Col_Code1
Col_Code2
Col_Code3
Col_Code4
Col_Code5
A283240
Code1
1
Null
Null
Null
Null
B490382
Code2
Null
1
Null
Null
Null
B490382
Code4
Null
Null
Null
1
Null
B449200
Code3
Null
Null
1
Null
Null
C243456
Code4
Null
Null
Null
1
Null
C243456
Code5
Null
Null
Null
Null
1
...
I have two table (Orders, Payments)
they are 1 to many relationships (1:N)
One order can have many payments
Table 1: Orders
id
total_price
1
1000
2
2000
3
3000
Table 2: Payments
order_id(FK of Orders)
amount_payed
1
500
2
2000
3
1000
3
500
3
750
some orders are...
A fairly common method of creating a series in T-SQL consists of using a CTE as the source, something like:
;WITH t AS
(
SELECT n = v2.n * 10 + v1.n
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9...
Updated in response to comments.
I have a table that consists of a record ID, a group ID (linking 1 or more records into a group) and a hash value for each record.
CREATE TABLE HashTable(
RecordID VARCHAR(255),
GroupIdentifier VARCHAR(255),
Hash VARCHAR (255),
GroupHashList VARCHA...
I can't upload images anymore: on the Ask Question page, the image upload dialog shows a red error message
Failed to upload image; an error occurred on the server
See the screenshot below (uploaded via imgur's own website):
The response from the server is
{"Success":false,"Error":5,"ErrorMessage"
