SQL Server performance: one-to-one join, or select within select? -


i have schema involves customers, facilities each customer, projects each facility, , items each project.

i want know if there general rule of thumb concerning performance of listing customer each item. 1 way think common is:

select     item.iteminfo     customer.customerinfo      item inner join      project on item.projectid = project.projectid inner join      facility on project.facilityid = facility.facilityid inner join      customer on facility.customerid = customer.customerid     (item filtering criteria)

each table indexed on primary , foreign keys.

however, in benchmarking, found more performant: 

select     item.iteminfo     (select top 1 customerinfo      customer      customerid = (select top 1 customerid                          facility                          facilityid = (select top 1 facilityid                                              project                                              project.projectid = item.projectid)                        )      )      item      (item filtering criteria)

is there reason difference? if so, how can utilize make other queries more performant?

thanks!

in questions sql, it's include ddl+dml sample data. i'll add script end of post use ctes generate bunch of test data.

after few tests, find joins more performant.

if run 2 queries against each other single item id (where item.itemid = 500, e.g.), each query has 50% cost.

itemid = 500

if use range - in case, itemid between 200 , 8000, query cost starts favor joins (19% 81%), , joins execute faster consistently.

itemid between 200 , 8000

i check speed this:

declare @start datetime set @start = getdate()  query 1  select getdate() - @start set @start = getdate()  query 2  select getdate() - @start

if increase range itemid between 200 , 80000, see more separation - query cost 5% 95% in favor of joins, , joins excute ~330 ms in scenario vs ~420 ms alternative query.

itemid between 200 , 80000

is there real unique clause? maybe there's sargability issue or something.

here's ddl/dml, creates 100 customers, ~1000 facilities, ~10000 projects, ~100000 items:

create table customer (customerid int primary key,customerinfo varchar(25)) create table facility (facilityid int primary key, customerid int foreign key references customer(customerid)) create table project (projectid int primary key, facilityid int foreign key references facility(facilityid)) create table item (itemid int primary key, iteminfo varchar(25), projectid int foreign key references project(projectid)) go  ;with cte (select 1 id, 'customer' + cast(1 varchar(5)) info union select cte.id + 1 id, 'customer' + cast(cte.id + 1 varchar(5)) cte  cte.id < 100) insert customer select id, info cte option(maxrecursion 100) go  ;with cte (select 1 id, 1 customerid union select cte.id + 1, ((cte.id + 1) / 10) + 1 cte  cte.id < 999) insert facility select id, customerid cte option(maxrecursion 1000) go  ;with cte (select 1 id, 1 facilityid union select cte.id + 1, ((cte.id + 1) / 10) + 1 cte  cte.id < 9989) insert project select id, facilityid cte option(maxrecursion 10000) go  ;with cte (select 1 id, 1 projectid, 'item' + cast(1 varchar(5)) iteminfo union select cte.id + 1, ((cte.id + 1) / 10) + 1, 'item' + cast(cte.id + 1 varchar(5)) cte  cte.id < 99889) insert item select id, iteminfo, projectid cte option(maxrecursion 0) go

Comments

Post a Comment

Popular posts from this blog

android - InAppBilling registering BroadcastReceiver in AndroidManifest -

i'm implementing inapp billing in android app. works well, however, i'm trying decouple broadcast receiver activity manifest. suggestion in android's trivialdrive sample: // important: dynamically register broadcast messages updated purchases. // register receiver here instead of <receiver> in manifest // because call getpurchases() @ startup, therefore can ignore // broadcasts sent while app isn't running. // note: registering listener in activity bad idea, done here // because sample. regardless, receiver must registered after // iabhelper setup, before first call getpurchases(). currently there's class extends broadcastreceiver : public class iabbroadcastreceiver extends broadcastreceiver { /** * intent action receiver should filter for. */ public static final string action = "com.android.vending.billing.purchases_updated"; private final iabbroadcastlistener mlistener; public iabbroadcastreceiver(iabbroadcastlistener listener) { m...
Read more

python Tkinter Capturing keyboard events save as one single string -

is possible store keyboard events 1 string? code below stores 1 char , prints it. card reader or bar code reader, contains collection of character/ string not 1 character @ time. goal save char pressed text variable. from tkinter import * root = tk() def key(event): text= event.char text+= event.char print ("pressed", text) def callback(event): frame.focus_set() print ("clicked at", event.x, event.y) frame = frame(root, width=100, height=100) frame.bind("<key>", key) frame.bind("<button-1>", callback) frame.pack() root.mainloop() currently, creating text variable , everytime key() function gets called, , text stores last character typed. you can define text module level variable , , use module level text inside key function - text = '' def key(event): global text text+= event.char print("pressed", text)
Read more

sql server - Why does Linq-to-SQL add unnecessary COUNT()? -

here tables: create table customer ( cid int primary key, name varchar(32) ) create table ord ( oid int primary key, cid int foreign key references customer, address varchar(20) ) here's linq-to-sql statement: var aaa = c in db.customer select new { c, o = c.ord.tolist() }; and here's inexplicable query generated linq-to-sql: select [t0].[cid] [cid], [t0].[name] [name], [t1].[oid] [oid], [t1].[cid] [cid2], [t1].[address] [address], (select count(*) [dbo].[ord] [t2] [t2].[cid] = [t0].[cid]) [value] [dbo].[customer] [t0] left outer join [dbo].[ord] [t1] on [t1].[cid] = [t0].[cid] order [t0].[cid], [t1].[oid] i want understand how rid of count(*) part. it's entirely uncalled for! linq needs count(*) in order determine number of entries returned o = c.ord.tolist() .
Read more