Re: Permance issues with migrated db - Mailing list pgsql-general

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.fldClientNumberLEFT OUTER JOIN 
                      dbo.tblClientRoomSize ON dbo.tblClientMaster.fldClientNumber =
dbo.tblClientRoomSize.fldClientNumberLEFT OUTER JOIN 
                      dbo.tblGeoPreference ON dbo.tblClientMaster.fldClientNumber =
dbo.tblGeoPreference.fldClientNumberLEFT OUTER JOIN 
                      dbo.tblClientProductRelation ON dbo.tblClientMaster.fldClientNumber =
dbo.tblClientProductRelation.fldClientNumberINNER JOIN 
                      dbo.tblProductMaster ON dbo.tblClientProductRelation.fldProductNumber =
dbo.tblProductMaster.fldProductNumberLEFT OUTER JOIN 
                      dbo.tblContactInfo ON dbo.tblClientMaster.fldClientNumber = dbo.tblContactInfo.fldClientNumber
LEFTOUTER 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