@ypercubeᵀᴹ Your comment "is the (id) the PK in every one of the 48 tables or not? How many rows does the query return for the single id (WHERE a.id=2)" was the most insightful and important IMO. It's a shame it was not responded to. Nine edits and 16 comments later, there is one very poor answer. sigh
I should do something about that, probably should have done earlier, but hey.
@dezso Maybe. I have no problem spending time on an answer to a "poor" question that is at least minimally clear, but there's nothing worse than writing an answer only to get a comment like "ah, I see I should have mentioned I am using robot penguins exclusively to execute my queries. Does that make a difference?".
@PaulWhite He made an edit at some point, so the answer is "yes".
> Edit3: Every table has pk(id). Basic design idea looks to be that there is main object in table "a" and then it may have one or several subtypes(tables from b to zz) of data. query returns 1 row and 2000+ columns.
300ms, 50 tables, that's about 6ms for each clustered index seek.
and the answer at -5, insists that it's right
I think dumping result in temp table has more advantage then NOLOCKS as dataset will be limited. NOLOCKS also helps because table is doing a self join (50 times apparently) so it will make reads quicker. Something I have personally experienced in similar scenario. — mouliin15 hours ago
@ypercubeᵀᴹ To be fair, the second query in their answer (the one actually trying to produce the required output) does consist of a series of self-joins.
@TomV It was a suggestion to try (and with bad syntax). Not sure but while LEFT JOIN / OUTER APPLY are equivalent in this case, it may yield different plans
There is at least some one else at this room that knows much more about the optimizer and how it works.
My wild guess in that q, is that it finds a plan with 50 cheap clustered index seeks. I'm not sure why it doesn't find that some (or many) of them can be totally omitted.
Perhaps the OP hasn't provided enough info. I don't even see a CREATE TABLE
That type = 'something' that they use may be a varchar(max) for all we know.
@TomV My suggestion in comments was (corrected):
OUTER APPLY (SELECT * FROM b WHERE a.id = b.id AND type = 'typeb') b
The op did not make that correction so the code in the question is useless
@TomV If there is indeed something to be gained in the situation, from doing 5 instead of 50 clustered index seeks, I'd also try to get the a row separately in a temp table or just the id and type in variables and have a second query for the rest 50 tables.
@TomV And I have no explanation for that 30% efficiency gain they claim.
Or why that plan has a Nested Loop.
The more I think about that q, the more I come to appreciate why such questions should be closed fast. so they can improved faster and not drag like this, with half info for days.
I have 3 tables
USERS ORDERS OFFERS
1 User has many Orders
1 Order belongs to User
1 Order has many Offers
1 Offer belongs to 1 Order
If someone could help me to draw this diagram....i need help.
I made it this far and i'm not sure if this is OK.
@dezso yeah. Going to get more practice indoor top-rope climbing over the winter, then do an indoor lead-climbing course around Christmas time, then a proper outdoor lead course up Stanage Edge in the spring
This is only different in missing preventing the users to reconnect (with a sufficiently eager application this can happen real fast, I've seen this a few times already). — dezso10 secs ago
@ypercubeᵀᴹ I did see that. Didn't cover the second question though? (number of rows for the where clause).
Anyway, I'm pretty sure he's asking about conditional joins, which I can say some things about. Perhaps when I get a few hours of answer time tomorrow.
@TomV Reading back, I'd just like to confirm what @ypercubeᵀᴹ said to you. Especially that without the details it's hard to know exactly what the question asker might be seeing. There are many possibilities, many of them interesting, but an answer that covered all of them would be quite time consuming to write.
@ypercubeᵀᴹ At the risk of pinging you for every comment you have written in the last 8 hours, yes, this. I am still not quite sure why I didn't close it as unclear early on. I'm not usually shy.
If 5 users votes for closing, that question will be closed immediately, at the same time if other users want to give answer to that question, they can’t because question is closed.
In that scenario it does not make a sense to close that question immediately.
Question should be close ...
Those 20-odd comments, ten question edits, and one (very poor) answer will serve as a reminder to me for some time to come why "close early, close often" is a good idea.
@TomV If you're interested in why an outer apply with an outer reference predicate can sometimes be better than a left join of similar construction, it's often because the apply predicate translates to a Filter with a start-up expression, whereas the join may evaluate the predicate at the join, after the inner side has run.
I do appreciate I may not have painted an entirely crystal clear picture with that explanation.
Right click the database and select Tasks then Import Data
Click Next button.
For Data Source
select Flat File Source.
select the CSV file.
Next > button.
For Destination, select the correct database
Enter the Server name;
check Use SQL Server Authentication,
enter the User name, Passw...
Right click the database and select Tasks then Import Data Click Next button. For Data Source select Flat File Source. select the CSV file. Next > button. For Destination, select the correct database Enter the Server name; check Use SQL Server Authentication, enter the User name, Password, and Da...
I've found answers on the web but also got conflicting answers from financial professionals (coworkers).
In metric, you'd use M (mega) for million, G (giga) for billion and T (tera) for trillion. The only financial specific similar abbreviation I can find is MM for million (financial notation, ...
Consider a MYSQL table with the following columns:
source_id integer
timestep integer
position integer
value float
This table contains slightly more than 1,000,000,000 rows. The data is read-only and serves as source for certain analyses.
What can be done to speed up the queries?
I've crea...
Hi I am trying to Update Column in a table with Different static date values.
I try to do an update statement like below
UPDATE f
SET f.CLOSEDATE = '2016-10-23'
FROM office f
inner join
(
select OFFICEID from paroffice
where active = 1
and RowStatus ='A'
AND DistrictID = 50000
)ofc
on ( f.O...
@PaulVargas well, the problem is usually more like how much is a billion (and not only in English speaking countries). Until this is not clarified, arguing about abbreviations is just for fun.
Suppose I have to export data from one server to another.
Best is to use
IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS
IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN
TO move data, depending on the amount/size of data and n/w bandwidth, L...
@JackDouglas Something like that would be better, yes. There are all sorts of things that can make linked server queries slow, including poor-quality drivers. If a plain SELECT through the linked server is not slow, the underlying problem is likely the implied distributed transaction created by the INSERT. It may also use a row-by-row cursor model for the row fetch in that case, depending on details.
If you can find a suitable disconnected approach (export then import as separate steps) that will almost always be faster, usually very significantly. Much time has been wasted by many people over the years trying to optimize linked server queries.
@PaulWhite If I just run SELECT * FROM LINKEDSERVER..ORAUSER.TABLE from SSMS the throughput is the same, so I guess there is some kind of rbar going on. With the Oracle client there is a parameter call arraysize — setting it high makes a huge difference to throughput.
I think we will try something that doesn't go through the linked server, thanks :)
@JackDouglas I'm no expert, but I seem to recall that some ways of connecting to Oracle from SQL Server provide much better throughput than others. I do not recall the details I am afraid, so I am glad you can try something that will avoid the whole issue :)
There are various ways to obtain multiple IDs with a single query in your situation.
First, you can just take the series of single SELECTs:
SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3;
SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6;
SELECT ID FROM FOO WHERE col1=7 AND col2=8 ...
@AndriyM Does this mean you are our resident SQLite expert now? ;)
I have 3 tables
USERS ORDERS OFFERS
1 User has many Orders
1 Order belongs to User
1 Order has many Offers
1 Offer belongs to 1 Order
If someone could help me to draw this diagram....i need help.
I made it this far and i'm not sure if this is OK.
If 5 users votes for closing, that question will be closed immediately, at the same time if other users want to give answer to that question, they can’t because question is closed.
In that scenario it does not make a sense to close that question immediately.
Question should be close ...
Consider a MYSQL table with the following columns:
source_id integer
timestep integer
position integer
value float
This table contains slightly more than 1,000,000,000 rows. The data is read-only and serves as source for certain analyses.
What can be done to speed up the queries?
I've crea...
Hi I am trying to Update Column in a table with Different static date values.
I try to do an update statement like below
UPDATE f
SET f.CLOSEDATE = '2016-10-23'
FROM office f
inner join
(
select OFFICEID from paroffice
where active = 1
and RowStatus ='A'
AND DistrictID = 50000
)ofc
on ( f.O...
Suppose I have to export data from one server to another.
Best is to use
IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS
IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN
TO move data, depending on the amount/size of data and n/w bandwidth, L...
There are various ways to obtain multiple IDs with a single query in your situation.
First, you can just take the series of single SELECTs:
SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3;
SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6;
SELECT ID FROM FOO WHERE col1=7 AND col2=8 ...
