Are the posted questions getting worse?
-
Sean Lange (6/5/2014)
I am just going to leave this here...http://www.sqlusa.com/bestpractices/[/url]
Don't worry there is soap for your eyes. Then if you read some of the content you may want to find a shotgun and just kill the internet. They even have a page that goes into great detail about the "best" way to construct nested cursors.
http://sqlusa.com/bestpractices2005/doublecursor/[/url]
:w00t:
God, is that piece of geocities cr*p still online?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
The Dixie Flatline (6/5/2014)
The 😉 was intended to denote sarcasm, guys.They have a punctuation symbol for that:
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
patrickmcginnis59 10839 (6/5/2014)
I'm going to be honest with you, as far as 2014 memory optimized tables(?) go, I don't know what standard behavior even is. I sure hope it isn't using 10 of 20 or so gigs for a table and having the server go belly up after a single user queries it a bit.10 GB of data, loaded into an in-memory table will use around 10 GB of memory. They are memory-resident tables, the entire table has to be in memory. Selects won't increase memory usage, modifications will. There's a garbage collector which runs periodically to clean up old versions of updated rows and deleted rows.
In short, if you are planning to have a 10GB table in-memory, you need a lot more than 10GB of memory. There needs to be the usual SQL buffer pool for the normal tables and SQL's normal operations, plus the 10GB for the table, plus the OS's requirements.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I think I have finally met the "exception on the rule" for SELECT *.
Select on an Oracle view with all the of the columns in the SELECT clause: 36 minutes (for 250,000 rows. Unfortunately I have no control over the view definition). SELECT * on same view: 2 minutes 50 seconds.
I am not kidding.
I talked with the Oracle DBA and he confirmed there are 2 different execution plans. The fast one is with nested loops, the second one with hashing.
He thinks that the SELECT * is faster because Oracle doesn't have to touch every table mentioned in the view to see if one of the selected columns is in there. When all the columns are explicitly mentioned, he has to check every table. Somehow this results in a vastly different execution plan.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
Koen Verbeeck (6/6/2014)
I think I have finally met the "exception on the rule" for SELECT *.Select on an Oracle view with all the of the columns in the SELECT clause: 36 minutes (for 250,000 rows. Unfortunately I have no control over the view definition). SELECT * on same view: 2 minutes 50 seconds.
I am not kidding.
I talked with the Oracle DBA and he confirmed there are 2 different execution plans. The fast one is with nested loops, the second one with hashing.
He thinks that the SELECT * is faster because Oracle doesn't have to touch every table mentioned in the view to see if one of the selected columns is in there. When all the columns are explicitly mentioned, he has to check every table. Somehow this results in a vastly different execution plan.
Koen - Is is repeatable? By that I mean can you select by column name, then *, then column name, then * and have the times consistent?
-
Ed Wagner (6/6/2014)
Koen Verbeeck (6/6/2014)
I think I have finally met the "exception on the rule" for SELECT *.Select on an Oracle view with all the of the columns in the SELECT clause: 36 minutes (for 250,000 rows. Unfortunately I have no control over the view definition). SELECT * on same view: 2 minutes 50 seconds.
I am not kidding.
I talked with the Oracle DBA and he confirmed there are 2 different execution plans. The fast one is with nested loops, the second one with hashing.
He thinks that the SELECT * is faster because Oracle doesn't have to touch every table mentioned in the view to see if one of the selected columns is in there. When all the columns are explicitly mentioned, he has to check every table. Somehow this results in a vastly different execution plan.
Koen - Is is repeatable? By that I mean can you select by column name, then *, then column name, then * and have the times consistent?
Yes. In fact, the SELECT * was part of an ETL load where everything ran in series. So for months, the load time was around 3 minutes.
Than I started experimenting with a parallel load, where I used SELECT columnnames. Suddenly the load time went over half an hour. Thinking there was some strange locking caused by the parallel load, I asked the Oracle DBA to investigate this. (The parallel load is running for 2 weeks now. Consistenly over 30 minutes). The DBA found the difference in execution plans, so I switched the parallel load to SELECT * and it went back to under 3 minutes.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
GilaMonster (6/6/2014)
patrickmcginnis59 10839 (6/5/2014)
I'm going to be honest with you, as far as 2014 memory optimized tables(?) go, I don't know what standard behavior even is. I sure hope it isn't using 10 of 20 or so gigs for a table and having the server go belly up after a single user queries it a bit.10 GB of data, loaded into an in-memory table will use around 10 GB of memory. They are memory-resident tables, the entire table has to be in memory. Selects won't increase memory usage, modifications will. There's a garbage collector which runs periodically to clean up old versions of updated rows and deleted rows.
In short, if you are planning to have a 10GB table in-memory, you need a lot more than 10GB of memory. There needs to be the usual SQL buffer pool for the normal tables and SQL's normal operations, plus the 10GB for the table, plus the OS's requirements.
Thats what I was thinking, so I thought he would at least have 10 gigs left after loading the table (and thats allowing the 3rd 10 gigs for the server although I doubt windows is going to use that much given that so far it was just him giving a few queries. He did say he had 30 gigs.
-
Koen Verbeeck (6/6/2014)
Ed Wagner (6/6/2014)
Koen Verbeeck (6/6/2014)
I think I have finally met the "exception on the rule" for SELECT *.Select on an Oracle view with all the of the columns in the SELECT clause: 36 minutes (for 250,000 rows. Unfortunately I have no control over the view definition). SELECT * on same view: 2 minutes 50 seconds.
I am not kidding.
I talked with the Oracle DBA and he confirmed there are 2 different execution plans. The fast one is with nested loops, the second one with hashing.
He thinks that the SELECT * is faster because Oracle doesn't have to touch every table mentioned in the view to see if one of the selected columns is in there. When all the columns are explicitly mentioned, he has to check every table. Somehow this results in a vastly different execution plan.
Koen - Is is repeatable? By that I mean can you select by column name, then *, then column name, then * and have the times consistent?
Yes. In fact, the SELECT * was part of an ETL load where everything ran in series. So for months, the load time was around 3 minutes.
Than I started experimenting with a parallel load, where I used SELECT columnnames. Suddenly the load time went over half an hour. Thinking there was some strange locking caused by the parallel load, I asked the Oracle DBA to investigate this. (The parallel load is running for 2 weeks now. Consistenly over 30 minutes). The DBA found the difference in execution plans, so I switched the parallel load to SELECT * and it went back to under 3 minutes.
That's interesting. I was thinking it could have been because one plan was cached and the other not, but if they're repeatable when both are cached, then it shoots my theory down. Granted, it's been years since I've worked with Oracle, so my knowledge is probably a bit obsolete, but I don't think database engines undergo a complete change between major versions.
What if you had the Oracle DBA create a materialized view instead of a straight view? It then acts like a table instead of a view, so you might be able to see some performance gains there. Please know in advance that I don't even have access to an Oracle database now, so anything I say is going to be based on memory only and nothing that's tested.
-
Ed Wagner (6/6/2014)
What if you had the Oracle DBA create a materialized view instead of a straight view? It then acts like a table instead of a view, so you might be able to see some performance gains there. Please know in advance that I don't even have access to an Oracle database now, so anything I say is going to be based on memory only and nothing that's tested.I'm not sure the Oracle DBA will be too happy with the materialized view, as it might affect production operations during the day (I read the data in at night).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
I don't know who to contact since Steve is on vacation, but I got the daily newsletter this morning and clicked on the link for "Is Powershell Worth the Time" and got "Can't find the page".
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams -
Stefan Krzywicki (6/6/2014)
I don't know who to contact since Steve is on vacation, but I got the daily newsletter this morning and clicked on the link for "Is Powershell Worth the Time" and got "Can't find the page".It works OK for me 🙂
Far away is close at hand in the images of elsewhere.
Anon. -
Ed Wagner (6/6/2014)
Koen Verbeeck (6/6/2014)
Ed Wagner (6/6/2014)
Koen Verbeeck (6/6/2014)
I think I have finally met the "exception on the rule" for SELECT *.Select on an Oracle view with all the of the columns in the SELECT clause: 36 minutes (for 250,000 rows. Unfortunately I have no control over the view definition). SELECT * on same view: 2 minutes 50 seconds.
I am not kidding.
I talked with the Oracle DBA and he confirmed there are 2 different execution plans. The fast one is with nested loops, the second one with hashing.
He thinks that the SELECT * is faster because Oracle doesn't have to touch every table mentioned in the view to see if one of the selected columns is in there. When all the columns are explicitly mentioned, he has to check every table. Somehow this results in a vastly different execution plan.
Koen - Is is repeatable? By that I mean can you select by column name, then *, then column name, then * and have the times consistent?
Yes. In fact, the SELECT * was part of an ETL load where everything ran in series. So for months, the load time was around 3 minutes.
Than I started experimenting with a parallel load, where I used SELECT columnnames. Suddenly the load time went over half an hour. Thinking there was some strange locking caused by the parallel load, I asked the Oracle DBA to investigate this. (The parallel load is running for 2 weeks now. Consistenly over 30 minutes). The DBA found the difference in execution plans, so I switched the parallel load to SELECT * and it went back to under 3 minutes.
That's interesting. I was thinking it could have been because one plan was cached and the other not, but if they're repeatable when both are cached, then it shoots my theory down. Granted, it's been years since I've worked with Oracle, so my knowledge is probably a bit obsolete, but I don't think database engines undergo a complete change between major versions.
The same sort of thing occasionally happens in SQL Server. The optimiser's imperfect, so occasionally, by making a trivial change that logically should be worse, or make no difference, you randomly end up with a better execution/explain plan at the end of it just because the logic path is different enough to end up with a different answer that performs better.
If you have the Tuning packs licensed, you could always ask your DBA to run Tuning Advisor against the statement with the columns listed and pin a better plan to it - obviously that has its own disadvantages as when you change any aspect of the statement, it'll revert to previous behaviour!
-
patrickmcginnis59 10839 (6/6/2014)
GilaMonster (6/6/2014)
patrickmcginnis59 10839 (6/5/2014)
I'm going to be honest with you, as far as 2014 memory optimized tables(?) go, I don't know what standard behavior even is. I sure hope it isn't using 10 of 20 or so gigs for a table and having the server go belly up after a single user queries it a bit.10 GB of data, loaded into an in-memory table will use around 10 GB of memory. They are memory-resident tables, the entire table has to be in memory. Selects won't increase memory usage, modifications will. There's a garbage collector which runs periodically to clean up old versions of updated rows and deleted rows.
In short, if you are planning to have a 10GB table in-memory, you need a lot more than 10GB of memory. There needs to be the usual SQL buffer pool for the normal tables and SQL's normal operations, plus the 10GB for the table, plus the OS's requirements.
Thats what I was thinking, so I thought he would at least have 10 gigs left after loading the table (and thats allowing the 3rd 10 gigs for the server although I doubt windows is going to use that much given that so far it was just him giving a few queries. He did say he had 30 gigs.
4GB in the first test, the one that failed with assorted errors.
As for the test on 32 GB, without knowing what else was running, what memory SQL was allowed to use (max server memory), hard to say much definitive. But his first test (11GB table loaded in-memory on a server with 4GB memory) had to fail.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
David Burrows (6/6/2014)
Stefan Krzywicki (6/6/2014)
I don't know who to contact since Steve is on vacation, but I got the daily newsletter this morning and clicked on the link for "Is Powershell Worth the Time" and got "Can't find the page".It works OK for me 🙂
I had a problem this morning when the thread kept loading after the page was displayed, the "connection was reset" in the banner and the "Add to Briefcase" wasn't rendering. I figured it was something with the web server talking to the database. A few minutes later, all was well.
-
Ed Wagner (6/6/2014)
David Burrows (6/6/2014)
Stefan Krzywicki (6/6/2014)
I don't know who to contact since Steve is on vacation, but I got the daily newsletter this morning and clicked on the link for "Is Powershell Worth the Time" and got "Can't find the page".It works OK for me 🙂
I had a problem this morning when the thread kept loading after the page was displayed, the "connection was reset" in the banner and the "Add to Briefcase" wasn't rendering. I figured it was something with the web server talking to the database. A few minutes later, all was well.
Yeah, it is working for me now too, must have just been a hiccup.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
Viewing 15 posts - 44,116 through 44,130 (of 66,712 total)
You must be logged in to reply to this topic. Login to reply