Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bobyliu
Starting Member
14 Posts |
 Posted - 2005-02-09 : 14:12:46
|
| alright several questions...just wondering if anybody knows that when you do inner joins on several tables, does MS sql server try to move the rows to the tempdb or a temp table first?the reason i am asking this was that i was that i was playing with MySQL last night and try to join 3 tables (70,000 rows,40,000 rows, 2,000 rows). and in the procss it was showing the query was trying to copy rows to a temp table. I know that in MS Sql thre is a tempdb, but i try to stay away from it when i can. not really sure how the backend works for MySQL though.Here is what i was trying to do:Tables:ZipData (id, zip code, county, latitdue, time zone, state, city, area code, etc) This table contains the unnormalized data I work on.County (id, county) new table for the normalizationCity (id, city, type) new table for the normalizationI created another table: CountyCity(CountyId,CityId) for the relations between the two data set.so here is the query i was trying to runinsert into CountyCity (countyid,cityid)select distinct County.id,City.idfrom ZipDatainner join county on county.county=zipdata.countyinner join city on city.city=zipdata.cityafter running this query last night, i think it is still running. is that just a performance issue with the server or MySQL itself? I am on a share hosting server with all other accounts. MySQL should be pretty powerful, but is it as powerful as MS SQL?so will it work better if i select from county and join with zipdata and city?Does Anybody know how to calculate the number of rows that the query actually uses?For example, 70,000 * 40,000 * 2,000 ? |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2005-02-09 : 14:21:52
|
MS sql server doesn't move rows to tempdb. it uses direct access to tables and indexes and joins those according to your condition.you can get the number of rows wit selece count(*) from (joined tables) or select @@rowcount.you can also see them in the execution plan.MySql is preety cool thing, but if i had to chose a free back end i'd use MSDE which is a toned down version of sql server.but as this is a sql server forum and not MySql forum i'm not overly objective on this  Go with the flow & have fun! Else fight the flow  |
 |
|
|
|
|
|
|
|
inner join