On Tue, 2007-05-22 at 17:21 +0100, Richard Huxton wrote:
> Robert Fitzpatrick wrote:
> > I have developed a view in pgsql that takes over 160K ms to execute, but
> > when copied into MS SQL against the old database (with syntax mods of
> > course), runs in a few seconds.
>
> Your query seems to produce 41.8 million rows. Are you sure MS-SQL is
> returning that many rows in a few seconds?
>
I thought the same thing. While I'm not a MS SQL guru, I put 'TOP 100
PERCENT' after SELECT in the query. The Enterprise Manager does not
indicate how many rows come back. I save it as a VIEW in MS SQL and do a
'select count(*)...' and, yes, it comes back 42164877 records.
Just to be sure MS SQL hasn't done something to the structure (I noticed
dbo prefixes, etc.), I pasted back into pgadmin, took off 'top 100
percent'. Then saved as a view and did a count(*) in pgsql, got
41866801. The difference in the number of records could vary due to the
dataset age I'm using in pgsql for migration. The MS SQL db is still
actively used. I hope I'm just missing something here...this are the
queries for mssql and pgsql, respectively...
SELECT TOP 100 PERCENT dbo.tblClientMaster.fldClientNumber,
dbo.tblClientMaster.fldClientName, dbo.tblClientMaster.fldClientType,
dbo.tblClientMaster.fldBuyingStatus,
dbo.tblClientMaster.fldSellingStatus,
dbo.tblClientProductPreference.fldFullService,
dbo.tblClientProductPreference.fldLimitedService,
dbo.tblClientProductPreference.fldAllSuite,
dbo.tblClientProductPreference.fldBudget,
dbo.tblClientProductPreference.fldConference,
dbo.tblClientProductPreference.fldResort,
dbo.tblClientProductPreference.fldDailyFee,
dbo.tblClientProductPreference.fldSemiPrivate,
dbo.tblClientProductPreference.fldPrivate,
dbo.tblClientProductPreference.fldMunicipal,
dbo.tblClientRoomSize.fldSize149,
dbo.tblClientRoomSize.fldSize299, dbo.tblClientRoomSize.fldSize449,
dbo.tblClientRoomSize.fldSize599,
dbo.tblClientRoomSize.fldSize600,
dbo.tblGeoPreference.fldSW, dbo.tblGeoPreference.fldNW,
dbo.tblGeoPreference.fldMW,
dbo.tblGeoPreference.fldW, dbo.tblGeoPreference.fldMA,
dbo.tblGeoPreference.fldSE, dbo.tblGeoPreference.fldS,
dbo.tblGeoPreference.fldNE,
dbo.tblProductMaster.fldProductName,
dbo.tblProductMaster.fldProductCode, dbo.tblContactInfo.fldContactNumber,
dbo.tblContactInfo.fldContactFirstName,
dbo.tblContactInfo.fldContactLastName, dbo.tblContactInfo.fldContactCity,
dbo.tblContactInfo.fldContactState,
dbo.tblContactInfo.fldContactZipCode,
dbo.tblContactInfo.fldContactTitle, dbo.tblContactInfo.fldContactPhone2_Type,
dbo.tblContactInfo.fldContactPhone2_Num,
dbo.tblContactInfo.fldContactPhone3_Num,
dbo.tblContactInfo.fldContactPhone4_Num,
dbo.tblContactInfo.fldContactPhone5_Num,
dbo.tblContactInfo.fldContactEMail, dbo.tblContactInfo.fldEnable,
dbo.tblContactInfo.fldContactPhone1_Num,
dbo.tblContactInfo.fldPersComments,
dbo.tblClientActivityTag.fldContactActivityTag
FROM dbo.tblClientMaster LEFT OUTER JOIN
dbo.tblClientProductPreference ON
dbo.tblClientMaster.fldClientNumber =
dbo.tblClientProductPreference.fldClientNumber LEFT OUTER JOIN
dbo.tblClientRoomSize ON
dbo.tblClientMaster.fldClientNumber = dbo.tblClientRoomSize.fldClientNumber
LEFT OUTER JOIN
dbo.tblGeoPreference ON
dbo.tblClientMaster.fldClientNumber = dbo.tblGeoPreference.fldClientNumber LEFT
OUTER JOIN
dbo.tblClientProductRelation ON
dbo.tblClientMaster.fldClientNumber =
dbo.tblClientProductRelation.fldClientNumber INNER JOIN
dbo.tblProductMaster ON
dbo.tblClientProductRelation.fldProductNumber =
dbo.tblProductMaster.fldProductNumber LEFT OUTER JOIN
dbo.tblContactInfo ON dbo.tblClientMaster.fldClientNumber
= dbo.tblContactInfo.fldClientNumber LEFT OUTER JOIN
dbo.tblClientActivityTag ON
dbo.tblClientMaster.fldClientNumber = dbo.tblClientActivityTag.fldClientNumber
SELECT tblclientmaster.fldclientnumber, tblclientmaster.fldclientname,
tblclientmaster.fldclienttype, tblclientmaster.fldbuyingstatus,
tblclientmaster.fldsellingstatus, tblclientproductpreference.fldfullservice,
tblclientproductpreference.fldlimitedservice,
tblclientproductpreference.fldallsuite, tblclientproductpreference.fldbudget,
tblclientproductpreference.fldconference, tblclientproductpreference.fldresort,
tblclientproductpreference.flddailyfee,
tblclientproductpreference.fldsemiprivate,
tblclientproductpreference.fldprivate, tblclientproductpreference.fldmunicipal,
tblclientroomsize.fldsize149, tblclientroomsize.fldsize299,
tblclientroomsize.fldsize449, tblclientroomsize.fldsize599,
tblclientroomsize.fldsize600, tblgeopreference.fldsw, tblgeopreference.fldnw,
tblgeopreference.fldmw, tblgeopreference.fldw, tblgeopreference.fldma,
tblgeopreference.fldse, tblgeopreference.flds, tblgeopreference.fldne,
tblproductmaster.fldproductname, tblproductmaster.fldproductcode,
tblcontactinfo.fldcontactnumber, tblcontactinfo.fldcontactfirstname,
tblcontactinfo.fldcontactlastname, tblcontactinfo.fldcontactcity,
tblcontactinfo.fldcontactstate, tblcontactinfo.fldcontactzipcode,
tblcontactinfo.fldcontacttitle, tblcontactinfo.fldcontactphone2_type,
tblcontactinfo.fldcontactphone2_num, tblcontactinfo.fldcontactphone3_num,
tblcontactinfo.fldcontactphone4_num, tblcontactinfo.fldcontactphone5_num,
tblcontactinfo.fldcontactemail, tblcontactinfo.fldenable,
tblcontactinfo.fldcontactphone1_num, tblcontactinfo.fldperscomments,
tblclientactivitytag.fldcontactactivitytag
FROM tblclientmaster
LEFT JOIN tblclientproductpreference ON tblclientmaster.fldclientnumber =
tblclientproductpreference.fldclientnumber
LEFT JOIN tblclientroomsize ON tblclientmaster.fldclientnumber =
tblclientroomsize.fldclientnumber
LEFT JOIN tblgeopreference ON tblclientmaster.fldclientnumber =
tblgeopreference.fldclientnumber
LEFT JOIN tblclientproductrelation ON tblclientmaster.fldclientnumber =
tblclientproductrelation.fldclientnumber
JOIN tblproductmaster ON tblclientproductrelation.fldproductnumber =
tblproductmaster.fldproductnumber
LEFT JOIN tblcontactinfo ON tblclientmaster.fldclientnumber =
tblcontactinfo.fldclientnumber
LEFT JOIN tblclientactivitytag ON tblclientmaster.fldclientnumber =
tblclientactivitytag.fldclientnumber;
--
Robert
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
