|
|
pffft, you call that a sand castle? this is a sand castle
They buy shoes, then they wear them! They make them sound old! Dairy! Dairy!
modified 20-Oct-19 21:02pm.
|
|
|
|
|
Stable Genius wrote: this is a sand castle
But can you live in any of those? 
Everyone is born right handed. Only the strongest overcome it.
Fight for left-handed rights and hand equality.
|
|
|
|
|
I was doing a query on a table with 8.8 million records, joining to a table with 2.7 million records, and then joining to a table with less than 1000 records, I got an OutOfMemory exception.
In the grand scheme of things, this ain't a huge dataset. It's nice to know that SQL Server is such a delicate house of cards...
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
Told you all, PostgreSQL is much better 
|
|
|
|
|
Well, that depends. A JOIN would combine all records of each set in all permutations. It would probably crash anywhere. Even during the calculations phase, it may be holding a shyteload of data. Profiler, anyone?
Yet, I know you know better than that. If my fearless leader were handy (An amazing DBA), he'd know how to make things right.
My first guess would be do it in two steps. First the 1K with one of the tables into a temp table and then the other table with the temp table.
| Ravings en masse^ |
|---|
| "The difference between genius and stupidity is that genius has its limits." - Albert Einstein | | "If you are searching for perfection in others, then you seek disappointment. If you are seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
SSMS, or SQL Server?
SQL Server is fairly robust. SSMS is the delicate house of cards.
If I had to guess, I'd say that SQL Server ran your query, and returned a huge number of rows. SSMS tried to display all the rows in the default grid view, and fell over.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote:
If I had to guess, I'd say that SQL Server ran your query, and returned a huge number of rows. SSMS tried to display all the rows in the default grid view, and fell over.
I bet this is what happened. And if so, that query may be returning way more data that Mister Mustang expected.
|
|
|
|
|
If it were all done with simple joins, it would be in the neighborhood of four quadrillion records (give or take).
| Ravings en masse^ |
|---|
| "The difference between genius and stupidity is that genius has its limits." - Albert Einstein | | "If you are searching for perfection in others, then you seek disappointment. If you are seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
Yeah JOINs have crashed or brought to a crawl many a database server in the history of programming.
|
|
|
|
|
JOIN is a anti-pattern. Never JOIN!!
This is marked as Joke, ok?
|
|
|
|
|
NoSQL for the win? 
|
|
|
|
|
Considering the number of times I've initiated or modified designs in order to make an object graph play nicely with a relational persistence mechanism...
It's a worthwhile question.
"Never attribute to malice that which can be explained by stupidity."
- Hanlon's Razor
|
|
|
|
|
I stand alone
I never JOIN
Free
#SupportHeForShe
Government can give you nothing but what it takes from somebody else. A government big enough to give you everything you want is big enough to take everything you've got, including your freedom.-Ezra Taft Benson
You must accept 1 of 2 basic premises: Either we are alone in the universe or we are not alone. Either way, the implications are staggering!-Wernher von Braun
|
|
|
|
|
In the bad guys voice from the Matrix (Agent Smith)...
As you can see, we've had our eye on you for some time now, Mr. Mustang. It seems that you've been living two lives. In one life, you're Mr. Mustang, software engineer for a respectable software company. You have a Social Security number, you pay your taxes, and... you help your landlady carry out the garbage. The other life is lived in computers, where you go by the hacker alias Outlaw Programmer, and are guilty of virtually every computer crime we have a law for. One of these lives has a future, and one of them does not. I'm going to be as forthcoming as I can be, Mr. Mustang. You're here because we need your help with a very large query. We know that you've been contacted by a certain individual: A man who calls himself SSMS. Now, whatever you think you know about this man is irrelevant. He is considered by many authorities to be the most dangerous man alive. My colleagues believe that I'm wasting my time with you, but I believe you wish to do the right thing. We're willing to wipe the slate clean. Give you a fresh start. And all that we're asking in return is your cooperation in bringing a known large data query issue to justice.
|
|
|
|
|
Took the afternoon off to get a head start on the weekend alcoholism?
|
|
|
|
|
I would like to say yes, but I don't drink anymore. No Nish, I am just insane.
|
|
|
|
|
I restarted simSSMSs and it performed the query without a problem - but it did take 35 minutes.
I took steps ( reduced the big table to 6.5 million rows and created a view that does the first join) and I got it down to 13 minutes.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
modified 21-Jul-18 7:56am.
|
|
|
|
|
I was going to point that out as well. Ultimately, SSMS is just a fancy front-end; the query itself is run elsewhere.
|
|
|
|
|
It was SSMS.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
https://support.microsoft.com/en-us/help/2874903
sqlcmd Utility | Microsoft Docs[^]
You are using the wrong tool. Use this instead to execute your query. This is a known issue with SSMS.
Output your results to a text file, etc. if you want to work with the data outside the console.
|
|
|
|
|
Maybe you doing an "out join" or "full join"? It's a full 3-d matrix in this case ...
It is not needed in most cases since the data in jointing tables are most likely to be correlated (via foreign keys) so that an (inner, left, right) join is most like what one needed. In this case, the db-engine or midwares only have records of order ~few 10 million to deal with, instead of order 10^7 x 3*10^6 x 10^3 records, which is huge ...
|
|
|
|
|
Worst date ever?
A man's solution on how to handle having two dates with two women on the same night ended up with him getting a ticket to jail.
"It is easy to decipher extraterrestrial signals after deciphering Javascript and VB6 themselves.", ISanti[ ^]
|
|
|
|
|
I would venture to guess that the "dates" he's really interested in will be in the jail, too. Later on he can make arrangements with the prison dating service. It's called "Drop the Soap"
| Ravings en masse^ |
|---|
| "The difference between genius and stupidity is that genius has its limits." - Albert Einstein | | "If you are searching for perfection in others, then you seek disappointment. If you are seek perfection in yourself, then you will find failure." - Balboos HaGadol Mar 2010 |
|
|
|
|
|
Yeah rape is hilarious. Let's laugh about rape.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
