|
Also friendly reminder for database system nerds that VLDB is next week and registration is free. The program page has videos of the talks already, so you only need to sign up if you want to ask questions in real-time I guess. The design of that page is loving awful though.
|
# ? Aug 28, 2020 17:03 |
|
|
# ? Jun 7, 2024 16:27 |
|
Star War Sex Parrot posted:That's cool because the academic system we're building aims for that. We compile physical plans to our DSL (a la MemSQL) and we can then either compile that using LLVM (a la HyPer 10 years ago, Postgres is adopting it for some queries now) to machine-native code for maximum performance, or we have an interpreter for that DSL for debugging. We're still working on getting the interpreter set up with a GDB stub, but in the meantime having the DSL alone is a nice way to see what operations are actually being performed at runtime. As usual the HyPer guys already built something similar for their new system. Yeah, that’s neat. Academically speaking that seems pretty interesting but unless you can read the compiled code (which might make sense to step through) I can’t imagine that correlating lines of lower level code to lines of pre-compile sql would be possible for anything except simple queries. Like I immediately think of nested views and a compiler taking advantage of a filtered column coming from a table a few layers deep in the views. Dunno. I’m no academic on the matter though.
|
# ? Aug 28, 2020 23:31 |
|
here's a weird one: let's say for stupid reasons I have a database with a date column that is, unfortunately, named date if i do this: select distinct top 10 date from dbo.MyTable order by date desc; it appears to work every time as expected if i do this: select distinct top 5 date from dbo.MyTable order by date desc; sometimes it gives me the results i expect; other times, it will repeat entries and not even bother ordering them wrapping the fieldnames in [] seems to have no effect - sometimes it works, sometimes it doesn't for some reason i don't have SHOWPLAN permissions on this machine so i can't actually look at an execution plan, but i've never seen something like this before. anyone got any ideas? this isn't affecting anything, it's much more of a curiosity but i'm eager to know if someone has a rational explanation e: i suspect it has something to do with the fact that in the second query, i'm retrieving less distinct records than exist, e.g. there are 8 distinct dates in the full dataset, and in the first query i am asking for 10 and thus getting all 8, whereas in the second query i'm only asking for 5 and then something freaks out??
|
# ? Aug 31, 2020 16:48 |
|
kumba posted:here's a weird one: let's say for stupid reasons I have a database with a date column that is, unfortunately, named date Can you reproduce it using a temp table with data that I could grab and poke at in my local instance?
|
# ? Aug 31, 2020 17:33 |
|
It sounds like a bug. What version of SQL server is it? Can you reproduce it on a test machine you have full privileges on? I tried to set it up on SQL Fiddle because they purportedly support SQL Server 2017, but it seems not to be working at the moment.
|
# ? Aug 31, 2020 20:11 |
|
Dumping all of the values into a temp table then doing the select distinct top 10 vs select distinct top 5 gives me the results I expect every time (or at least every time I've run it so far) I did learn something: dbo.MyTable in this case isn't actually a table, it's a view (the team left out that detail when I was looking at this this morning) I'm not exactly sure why it being a view would make any difference, but I'm guessing there's something weird going on behind the scenes when ordering by columns materialized in a view?? I dunno, I would still expect this to work but unfortunately I also can't see the underlying data nor the query that populates this view so I'm shooting a bit in the dark here
|
# ? Aug 31, 2020 20:25 |
|
Is the underlying data type actually date? Are there unions involved in the view?
|
# ? Sep 1, 2020 05:40 |
|
I'm trying to store some data with "column expressions" into a SQLite database. Basically I've got a bunch of families of things, where each family is a table of different family options. Some of the columns have expressions like code:
code:
code:
code:
code:
code:
|
# ? Sep 5, 2020 18:36 |
|
This the script I've been told to use by my director to get the correct data:code:
|
# ? Sep 10, 2020 16:50 |
|
what on god's green earth
|
# ? Sep 10, 2020 18:19 |
|
why change it? it works!!
|
# ? Sep 10, 2020 18:47 |
|
Nice formatting style
|
# ? Sep 10, 2020 20:00 |
|
Let's see here: SELECT DISTINCT, nestled inline views, implicit join syntax, aggregation of aggregation, aliases both bland and reused repeatedly. And to top it all off, a set operation. A wild, wild ride in just over 1500 characters. Have we found it? Is this the worst SQL query?
|
# ? Sep 10, 2020 20:18 |
|
I ran this through an formatting plugin. I have no idea what it does. Seems to UNION two the same queries? There's probably a minute difference between the two, but I don't see it. --edit: A different table in each query.
|
# ? Sep 10, 2020 20:33 |
|
Combat Pretzel posted:I ran this through an formatting plugin. I have no idea what it does. Seems to UNION two the same queries? There's probably a minute difference between the two, but I don't see it. --edit: A different table in each query. Lastjob had that all over the place because we had one generic 'registration' table for three types of reservation, all of which were often lumped together in the UI and reporting. At least they could often simply be turned into UNION ALLs for low-hanging performance gains.
|
# ? Sep 11, 2020 13:33 |
|
Yeah, uh, I'm only just about getting back into SQL after two decades of gently caress-all of it, but, can't you just create a view over all the tables and avoid this type of bullshit?
|
# ? Sep 11, 2020 19:02 |
|
How big is too big for Postgres indexes? I'm trying to drop into an application that's struggling that I know little about, and improve performance. I've got a 15GB data, ~12.5 million row table with no relations. The hottest index is >2.5GB, and individual queries using that index are both slow (>1.5s) and driving high CPU usage. The problem query is definitely only doing an index scan with very low planning time, but execution time is high. The index is a 4 column multi-column index, we're querying on 3 of them in the right order so the index is being used. The columns that are being queried on are highly duplicated, and also text fields. 2 of the 3 columns we're querying with have a total of 6 distinct values each, so my immediate thinking is that we can reduce the index size by normalizing those out into their own table, so that instead of duplicated text making the index huge we just have numeric foreign keys. Any suggestions for different kinds of indexes that would perform better with such duplicated values in the index? It's just a normal B-Tree right now. Edit: Actually, I would bet that putting the least-duplicated column first in the multi-column index helps... let me try that. Twerk from Home fucked around with this message at 03:49 on Sep 15, 2020 |
# ? Sep 15, 2020 03:40 |
|
sql server question can one retrieve the history for a particular login? to be specific, i'd want to know all of the times the login logged on, what queries it ran, any ddl changes, dml changes, etc. i don't think this is possible at all? i know sql server keeps like the last day's worth of queries for all logins in dm_exec_query_stats and dm_exec_sql_text, but beyond that...i don't think so. if i wanted to produce an audit trail, i think i would need to create an audit for that login. that would then save all of the login's actions to some file or table or something. or i could run a temporary profile on them, but that seems silly for what i'm being asked for. there's not some grand history log or table tracking all changes as far as i know. am i understanding this correctly?
|
# ? Sep 16, 2020 15:19 |
|
Twerk from Home posted:How big is too big for Postgres indexes? Twerk from Home posted:The columns that are being queried on are highly duplicated Look into gin or gist indexes. They are inverted so will be much smaller and more efficient for duplicated values. See this talk for details on how it works under the hood https://youtu.be/2l-nCkPQVuQ Twerk from Home posted:
If you can get away with it, try the “C” collation. The downside is non ascii characters won’t sort to the expected place, but it might not matter (in almost all my apps almost never). Even when I have non ascii text, I’m not relying how Postgres sorts it, since it gets sorted again in my app or it just doesn’t matter for whatever reason. If this is the case here, and the collation is C, you can take advantage of the “abbreviated keys” optimization that went in a few releases ago.
|
# ? Sep 23, 2020 07:47 |
|
Pardot posted:Large indexes are fine as long as (and I’m assuming this is oltp) you have enough ram to keep cache hit rates at 99% I really appreciate this, thank you. I'm going to start by hitting the docs about GIN indexes.
|
# ? Sep 23, 2020 14:45 |
|
Pardot posted:Large indexes are fine as long as (and I’m assuming this is oltp) you have enough ram to keep cache hit rates at 99% Lastjob: "SQL performance is worse but we're paying extra for the faster [smaller] RAM! How can this be?" Me: *points at cache churn, disk read stats* Lastjob: "But fast RAM!?"
|
# ? Sep 23, 2020 18:18 |
|
Are there any proper Hasura / PostgREST equivalents for SQLite or other embedded databases? By "proper" I mean that they don't just generate CRUD endpoints, but also eg. perform auth via SQL roles, expose webhooks, and in general can do most of what you'd expect from a full backend.
|
# ? Sep 23, 2020 23:32 |
|
NihilCredo posted:Are there any proper Hasura / PostgREST equivalents for SQLite or other embedded databases? By "proper" I mean that they don't just generate CRUD endpoints, but also eg. perform auth via SQL roles, expose webhooks, and in general can do most of what you'd expect from a full backend. It's not as full-featured at PostgREST, but I've made a Go library that creates a similar HTTP API at runtime from either SQLite or MariaDB. It's capable of doing custom authentication and can tack on the proper user ID column to queries to control access. You could create your own webhooks by wrapping the HTTP handlers it produces in your own code before feeding them to the server.
|
# ? Sep 24, 2020 17:39 |
|
Using SSMS, is there a shortcut/add-in that allows you to enclose values in single quotes for use in an IN clause? Right now, I put my values in Excel and create this formula in column B: ="'" & A1 & "',". This results in: code:
code:
Edit: I found this on Stackoverflow: https://dba.stackexchange.com/questions/96371/applying-quotes-across-multiple-lines. But the Shift-Alt solution only seems to do this for one line at a time. Not practical if I have 1000+ lines. It also doesn't work if my values aren't all the same length. Secx fucked around with this message at 16:18 on Sep 25, 2020 |
# ? Sep 25, 2020 16:08 |
|
Secx posted:Using SSMS, is there a shortcut/add-in that allows you to enclose values in single quotes for use in an IN clause? i jam 'em into notepad++ and do a regex replace. something like find ^(.+)$, replace with '$1', i've also done similar things to split up text into multiple columns for inserting into temp tables or w/e
|
# ? Sep 25, 2020 23:47 |
|
I use LINQPad and some extensions I wrote to mush the data into shape
|
# ? Sep 29, 2020 18:17 |
|
In DB2/DB400, when I want to use CTEs, do I need to end the whole query with a semicolon? I've been sending queries via ODBC without any ending semicolons since a while now, and everything's fine. Then I tried to get CTEs work, just for the dumb iSeries to puke out (to me) non-sensical error messages. Out of boredom and annoyance I'm googling about it at home, and they're all putting an ending semicolon in the examples. Is that the cause of my frustration? (Then again, lots of posts have semicolons in simple stupid queries, so what the hell.)
|
# ? Oct 6, 2020 21:36 |
Hey thread, wondering if you can help me figure out a very broad and newb-ish question regarding SQL: I'm pretty new to SQL (though I know enough to have figured out this is the SQL thread, so there's that) and still wrapping my head around how exactly it would get used in a real-world application. I work for a small business that's currently operating pretty slowly due to COVID, so I'm taking some time to look at our business processes and figuring out if there's a better way to set up some of our systems. I work for a company that rents audio equipment, so we've got a big, varied inventory of stuff that requires pretty constant tracking, and right now we use like three different systems to handle orders, inventory tracking, and invoicing. When I took over the day-to-day order management, we were still using a Lotus 1-2-3 spreadsheet to track inventory, and the thing was basically useless. I now have a very hacky Excel spreadsheet for tracking our inventory, and the upside is that it not only tells me what quantities of equipment we have in the shop right now, but actually generates an availability calendar, and lets me plug in dates and item codes to see if something will be available in the future! Amazing! (I know it's not amazing, but the old spreadsheet literally couldn't do that, you just had to guess or figure it out yourself unassisted). This is working, sort of, but I'd like a much more streamlined system that lets me enter a customer name, rental out date, return date, items, and quantities into an entry form, and have whatever program I'm working with automatically adjust inventory availability for that date range. I'd obviously like to be able to call up inventory availability at any time in the future to see what we can actually commit to future rentals, too. Originally, I was going to try to build something in MS Access to do all of this, but I took the Codecademy SQL class on a whim and it makes a lot of sense to me. So should I go that route? The thing I haven't wrapped my head around is, how do my (non-coder, mostly tech literate) coworkers interact with this theoretical database? If I build the database, and use SQL to build the kinds of queries I need for regular use, do I use some sort of "end-user" application that refers to that database and presents it all in a clean, readable spreadsheet-style interface for them to refer to? Basically I don't think I've totally wrapped my head around the relationship between SQL vs something like MySQL vs an "end-user" database program like Access, and so far all my newbish googling comes up with results that don't quite make the situation clearer to me.
|
|
# ? Oct 12, 2020 17:12 |
|
Access is a basic database and provides a forms interface, can be set up so users must go through the forms, etc. It's been a couple decades since I've touched it, so I don't rightly know how hard it is to build something, if it can be linked to a website form/interface, and how awful the learning curve is. As a benefit, it's probably the easiest choice for Excel import/export support. Keep in mind that your users will refuse change. Better validation and reporting scripts in your existing spreadsheet might go a long way to improving the process without having to fight your peers while simultaneously learning an entirely new collection of technologies. The primary difference you'll find between the spreadsheet and SQL land is that the latter does not just let you "randomly enter some text in the next column because we're giving the customer a pizza with their pickup". Everything has to be well defined (the "schema") and, in most cases, values must be appropriate or selected from a list of alternates. Of course unbounded comment fields work, but if your users start putting business decisions in there, it will be a mess. One other quick note. SQL is not scheduling software. Because it's "general purpose", it does not tie you to specific solutions, so you have to choose a method to implement reservation uniqueness, calendar control, and so forth. MySQL data types will limit you here somewhat compared to PostgreSQL, for example, which supports range types and uniqueness constraints on timestamp ranges. Much depends on the granularity and options that you have for reservations, as well as your checkout workflows and modifications to existing reservations.
|
# ? Oct 13, 2020 08:40 |
|
ms access is probably fine for you. like potc said, access is basically a sql database combined with a forms interface that should be familiar to excel users, so basically you are getting the end-user application bundled with your database. you can probably buy something similar from oracle too, for $$$$$. but with popular open source databases like mysql and postgres, what you get is the backend database and that's it. with an access database, the db is just a file on your personal computer, and you use the access application to... access it. with mysql or postgres, the db is a separate server that runs on another machine, and there is no built-in graphical interface. generally the way you build your end-user application with these databases is to hire some programmers. technically you could use a database client like pgadmin or phpmyadmin to interact with the database in a graphical way. however, those clients are really intended for database administrators and programmers, not end users.
|
# ? Oct 13, 2020 22:22 |
|
DELETE CASCADE posted:ms access is probably fine for you. To be honest, access has enough of a learning curve that I'm not sure how much better off you'd be with access over just installing a database like mysql or postgres and using those front end tools like phpmyadmin or pgadmin. I wouldn't say the UIs for pgadmin/phpmyadmin are very good, but access is opaque and using access doesn't translate to any other program.
|
# ? Oct 14, 2020 17:01 |
|
Bruegels Fuckbooks posted:To be honest, access has enough of a learning curve that I'm not sure how much better off you'd be with access over just installing a database like mysql or postgres and using those front end tools like phpmyadmin or pgadmin. I wouldn't say the UIs for pgadmin/phpmyadmin are very good, but access is opaque and using access doesn't translate to any other program. Nonsense, if they use Access then the next person to deal with it gets to curse their name for all eternity
|
# ? Oct 14, 2020 18:11 |
|
The main thing that separates Access from client-server model RDBMSes as I understand it, is that Access isn't designed for multiple people to interact with it at a time. If the DB is only going to be used by the business's employees (one at a time, ideally sitting at a specific computer) then Access is probably just fine. If you're going to have multiple users interacting with it or multiple programs reading/writing then Access is probably not a good idea. If you think you will eventually want to add (for example) a website that customers can use to order or book stuff, then skip Access and go straight to a grown-up database.
|
# ? Oct 15, 2020 11:04 |
|
It sounds like you're looking for AirTable or one of the many similar products, more than a pure database.
|
# ? Oct 15, 2020 11:25 |
|
Crossposting: Is there some kind of magical galaxy brain way to do SQL that I'm missing? I learned to do SQL a certain way and I'm at a loss as to why I got rejected after a technical take-home. The thing is that I know the answers are correct because I uploaded the tables into BigQuery to test all of my answers there. I generally use table aliases / common table expressions in order to avoid using subqueries, and I avoid using self joins to filter data since they're generally harder aesthetically to read (unless there's a parent-child relationship in the same table). My syntax and methods are "highest ranked answer on Stack Overflow". After inheriting a ton of bad queries from the senior analyst, I went through and organized everything in such a way so anyone from top level down could make sense of my code even if they had no idea how to write SQL, including documentation that explained what everything did. But how I functionally write code on a job is apparently different from what a recruiter wants in an interview so I'm at a loss here I'm in my final rounds with another job I am excited for but I want to cover my bases nonetheless. Any ideas?
|
# ? Oct 21, 2020 01:34 |
|
Knot My President! posted:Crossposting: If this is re: your post in poo poo that pisses me off, the hirers were insane. Doubly so if you were on new Postgres, because they've taken the pattern matching feature that Oracle put in the spec and implemented it, so there's a really performant way to do trend finding now rather than hideous joins. Edit: Also table aliases are free, they're just letting you shorthand a name, just like renaming columns in a result set.
|
# ? Oct 21, 2020 08:06 |
|
Wait they implemented match recognize??? Holy poo poo It's literally regex for everything
|
# ? Oct 21, 2020 14:45 |
|
Is there an obvious way I'm missing to export a Postgres table with JSONB columns as a nested JSON object? I'm exporting now using several methods and they all dump that column out as an escaped JSON string.
|
# ? Oct 21, 2020 16:31 |
|
Hey all I have a similar question to the user above and I'm not sure if this is the best place for the question but: I have a client that wants to be able to have a handful (20?) of their employees have some kind of UI to enter in certain data, which then hits a database which can be reported on with charts. The "catch" is some of that data needs to be exported to be used within a WordPress/Javascript site to display as basically time availability etc. I'm guessing the answer is to do a postgres database which I could then access via Javascript (right?), but is there a good reporting tool that will grab the data and display it for them that is friendly to use? I barely know SQL and have a basic understanding of Javascript, but I have until January to learn and deploy this. That said if any of you know of any "all in one" solutions I don't mind using something like that I. E. Access. I guess the most important thing is that it allows people to log in and submit data through a form or profile and then for it to have data be reportable and exposed for display. Thanks goons
|
# ? Oct 21, 2020 18:14 |
|
|
# ? Jun 7, 2024 16:27 |
|
I am not very aware of it myself, but GQL sounds like it eoukjd be appropriate for that.
|
# ? Oct 21, 2020 18:17 |