Page 1 of 2 12 LastLast
Results 1 to 15 of 27

Thread: Reporting help

  1. #1
    Join Date
    May 2007
    Posts
    49

    Unanswered: Reporting help

    Hi everyone,

    I am looking for reporting solution / technique that can effectively produce web reports consists of millions of records. I am using SQL Sever 2005 for data stroage. There is one main table having around 50-60 columns and
    15,00,000 records in it. This table is involved in all report select queries and also almost each report query consists of around 10-15 tables.

    I have tried many solutions and tools like simple gridview, SQL Server Reporting services, Crystal Reports, SSIS etc. I also created a separate de-normalized database for reporting replicated from Production DB. But nothing works as expected

    I am not sure whether this is a correct forum to post this problem, but can anybody help please ?

    Thanks
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to explain what "nothing works as expected" means. What did you expect? What happened instead? Do you really need to output millions of records via the web. Do you actually need to produce aggregations of these millions of records?

    I can see how gridview, Crystal, RS are related, but how on earth would you relate SSIS to one of these?

    I'm not clear what the problem is here: creating the data to feed the report or creating a report to display this data.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2007
    Posts
    49
    Hi,

    Thanks for reply, nothing works as expected means I was expecting a good performance but almost all reports takes around 10-20 mins. to run (many times app. throws timeout error). Not only time is the problem.. if 10-15 users requests same report at a time, server stops responding.

    I know, showing millions of records on web is not logical but its a user requirement.. don't know what they do with it.

    1. creating the data to feed the report
    2. creating a report to display this data

    both are the problems for me.. neigher I am able to create data effeciently nor able to display it.

    Firstly we used SQL Reporting services as it worked quite well with around 1 million records when I tested it by creating test report. But next year data increased much much more then my anticipation and SQL Reporting Services failed. When I searched microsoft's site I found a suggestion to use SSIS.

    I am using SSIS at two places -

    1. to export report data to Excel (xls) file and asking user to download it instead of showing data on web page.

    2. to populate de-normalized reporting database from production database. This SSIS is scheduled to run once in a day (in non working hours). SSIS identify new / updated records with the help of an additional column - updateflag (int) added to each table and uses Slowly Changing Dimention task to insert update records.

    Performance of this SSIS is also a big problem for me , It takes around 8-10 hours to sync. both databases.

    There are number of user defined functions used in select queries

    E.x. -

    Code:
    CREATE Function [dbo].[funcGetAllDisks]
    (
    	@strTrackID VARCHAR(100)
    )
    RETURNS VARCHAR(8000)
    AS
    BEGIN
    DECLARE @BLSvalues VARCHAR(8000)
      SELECT @BLSvalues = COALESE(@BLSvalues, '') + ' + '  +CAST(tbl_BLS_HDD.HDDSize AS VARCHAR(20)) 
      FROM  tbl_BLS_Report
      WHERE TrackID=@strTrackID 
    RETURN @BLSvalues
    END
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - still not totally getting it. Excel can only store 65k rows, so how can it be an alternative to displaying millions of rows on a web page?

    Quote Originally Posted by mihirclarion
    I know, showing millions of records on web is not logical but its a user requirement.. don't know what they do with it.
    I would really strongly suggest you schedule a meeting with the users. Without knowing what they do with things you don't really know the user requirement. There is almost certainly some information they have that will help you.


    Quote Originally Posted by mihirclarion
    1. creating the data to feed the report
    2. creating a report to display this data

    both are the problems for me.. neigher I am able to create data effeciently nor able to display it.
    let us concentrate on #1 then for now.


    Quote Originally Posted by mihirclarion
    Code:
      SELECT COUNT(*)
      FROM  tbl_BLS_Report
      WHERE TrackID=@strTrackID
    What is the typical result of this query?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2007
    Posts
    49
    Excel situation is handled by ASP.NET shared assembly added to SSIS, this assembly creates new worksheet and change destination accordingly after exporting 65000 rows.


    Code:
    SELECT COUNT(*)
      FROM  tbl_BLS_Report
      WHERE TrackID=@strTrackID
    -this query will return value less then 4 in 99% cases.


    I have already discussed about requirements with my point of contact (a person working as support supervisor in client organisation). He just told me that they need it for some accounting puropse but he is not very much interested in telling more in details about it.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mihirclarion
    Excel situation is handled by ASP.NET shared assembly added to SSIS, this assembly creates new worksheet and change destination accordingly after exporting 65000 rows.
    You mean it creates tens of excel spreadsheets?

    Quote Originally Posted by mihirclarion
    Code:
    SELECT COUNT(*)
      FROM  tbl_BLS_Report
      WHERE TrackID=@strTrackID
    -this query will return value less then 4 in 99% cases.
    In that case this should fly if correctly indexed. Can you post the FULL DML for tbl_BLS_Report please?

    Quote Originally Posted by mihirclarion
    I have already discussed about requirements with my point of contact (a person working as support supervisor in client organisation). He just told me that they need it for some accounting puropse but he is not very much interested in telling more in details about it.
    Is the guy pressuring you to improve things? If so then this is your lever to get him interested.

    The long and the short of it is if you *must* export millions of rows then tough - it isn't going to fly. We can tweak things and make it more efficient but the simple fact is that writing several GB to disk and\ or passing it around LANS\ WANS\ web takes time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2007
    Posts
    49
    Yes thats right, SSIS creates number of excel spreadsheets.. that is why I am looking for better solution. Support supervisor is not pressuring me to improve things but from support reqests, I can clearly see that end users are not happy with the things.

    It is true that writing millions of records to disk and passing those through LAN / WAN is going to take time and that is somewhat acceptable also, what I am more concerned about is time that SSIS is taking to sync. databases.

    Here is the DML of one of the most time consuming SPs used in SSIS -

    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER Procedure [dbo].[Trans_UnitBLSInsert]
    As
    Begin
    
    	Select Distinct
    	vwTransferUnits.UID AS UnitID,
    	tbl_ActivityRequest.ID AS RequestId,
    	vwTransferUnits.TrackId AS TrackId,
    	vwTransferUnits.SerialId AS SerialId,
    	vwTransferUnits.Asset1ID as Asset1ID,
    	vwTransferUnits.Asset2ID as Asset2ID,
    	vwTransferUnits.Asset3ID as Asset3ID,
    	vwTransferUnits.Asset4ID as Asset4ID,
    	dbo.TransfnGetHDD(vwTransferUnits.TrackId)   + ' (Erasure Data)'   AS BLSDisk_Disk,
    	dbo.TransfnGetHDDSerial(vwTransferUnits.TrackId)   + ' (Erasure Data)'  AS BLSDisk_Serial,
    	dbo.TransfnGetHDDSize2(vwTransferUnits.TrackId)    + ' (Erasure Data)'  AS BLSDisk_Size,
    	dbo.TransfnGetDisplaySerial(vwTransferUnits.TrackId)   + ' (Erasure Data)'   AS BLSDisplay_Serial,
    	dbo.TransfnGetFloppyDisks(vwTransferUnits.TrackId)    + ' (Erasure Data)' AS BLSFloppy_Product,
    	dbo.TransfnGetFloppySerial(vwTransferUnits.TrackId)    + ' (Erasure Data)' AS BLSFloppy_Serial,
    	dbo.TransfnGetOpticalSerial(vwTransferUnits.TrackId)    + ' (Erasure Data)' AS BLSOptical_Serial,
    	dbo.TransfnGetOpticalMemory(vwTransferUnits.TrackId)   + ' (Erasure Data)'  AS BLSOptical_Memory,
    	dbo.TransfnGetOpticalSpeed(vwTransferUnits.TrackId)   + ' (Erasure Data)'  AS BLSOptical_Speed,
    	dbo.TransfnGetProcessorName(vwTransferUnits.TrackId)    + ' (Erasure Data)'  AS BLSProcessor_Name,
    	dbo.TransfnGetProcessorSpeed(vwTransferUnits.TrackId)   + ' (Erasure Data)'  AS BLSProcessor_Speed,
    	dbo.TransfnGetmb_chipset(vwTransferUnits.TrackId)   + ' (Erasure Data)'   AS BLS_MBChitSet,
    	dbo.TransfnGetBLSProduct(vwTransferUnits.TrackId)   + ' (Erasure Data)'  AS BLS_Product,
    	tbl_BLS_Report.Product AS BLS_Product_Int,
    	tbl_BLS_Report.Created As BLS_Report_Created,
    	dbo.TransfnGetMb(vwTransferUnits.TrackId)    + ' (Erasure Data)'  AS BLS_Memory,
    	dbo.TransfnGetBiosSerial(vwTransferUnits.TrackId)   + ' (Erasure Data)'  AS BLS_BiosSerial,
    	dbo.TransfnGetBiosMake(vwTransferUnits.TrackId)   + ' (Erasure Data)'  AS BLS_BiosMake,
    	dbo.TransfnGetBiosModel(vwTransferUnits.TrackId)    + ' (Erasure Data)' AS BLS_BiosModel,
    	dbo.TransfnGetAssetTag(vwTransferUnits.TrackId)    + ' (Erasure Data)' AS BLS_AssetTag,
    	dbo.TransfnGetNoHardDisks(vwTransferUnits.TrackId)    + ' (Erasure Data)' AS BLS_NoHardDisks,
    	dbo.TransfnGetSnapShotSerialNo(vwTransferUnits.TrackId)  AS SnapShot_SerialId,
    	dbo.TransfnGetSnapshotAsset1ID(vwTransferUnits.TrackId) AS SnapShot_Asset1Id,
    	dbo.TransfnGetSnapshotAsset2ID(vwTransferUnits.TrackId) AS SnapShot_Asset2Id,
    	dbo.TransfnGetSnapshotAsset3ID(vwTransferUnits.TrackId) AS SnapShot_Asset3Id,
    	dbo.TransfnGetSnapshotAsset4ID(vwTransferUnits.TrackId) AS SnapShot_Asset4ID,
    	case
    		When tbl_UnitType.UnitType = 'Monitor' Then 
    			Case 
    			  When tbl_PartsMaster.CPUSpeedMonitor is not NULL then tbl_PartsMaster.CPUSpeedMonitor + ''
    			  Else tbl_PartsMaster.CPUSpeedMonitor
    			End	
    		When tbl_UnitType.UnitType = 'Laptop' Then 
    			Case
    			  When vwTransferUnits.LTPScreenSize is not NULL then  tbl_PartsMaster.CPUSpeedMonitor + ''
    			  Else tbl_PartsMaster.CPUSpeedMonitor
    			End
    	End  AS ScreenSize,
    	vwTransferUnits.deleted as deleted,
    	dbo.TransfnGetBLSRef(vwTransferUnits.TrackId)   + ' (Erasure Data)'  AS BLS_Reference,
    	dbo.TransfnGetOpticalProduct(vwTransferUnits.TrackId)    + ' (Erasure Data)' as BLSOptical_Product,
    	dbo.TransfnGetMACAddress(vwTransferUnits.TrackId) + ' (Erasure Data)' As BLSReport_MAC,
    	dbo.TransfnGetUnique_id(vwTransferUnits.TrackId)  + ' (Erasure Data)' AS BLSReport_UNIQUE_ID,
    	dbo.TransfnGetCLNSECONDS(vwTransferUnits.TrackId)  + ' (Erasure Data)' AS BLSReport_CLEANER_SECONDS	
    	from vwTransferUnits
    	LEFT OUTER JOIN tbl_UnitType ON vwTransferUnits.UTCODE = tbl_UnitType.CODE
    	LEFT OUTER JOIN tbl_PartsMaster ON vwTransferUnits.OEMID = tbl_PartsMaster.ID 
    	LEFT OUTER JOIN tbl_ActivityRequest ON vwTransferUnits.ACTID = tbl_ActivityRequest.ActID 
    	LEFT OUTER JOIN tbl_BLS_Report ON tbl_BLS_Report.TrackID = vwTransferUnits.TrackID 
    	LEFT OUTER JOIN tbl_SnapshotUnits ON tbl_SnapshotUnits.TrackID = vwTransferUnits.TrackID
    End
    View -
    Code:
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER VIEW [dbo].[vwTransferUnits]
    AS
    SELECT     
    UID, -- Primry key 
    UTCode, 
    SUTCode, EquipmentTitle, ACTID, RPalletID, PalletID, 
    Location, LastLocationBeforeDespatch, 
    Status, TrackID, Condition, SerialID, 
    Asset1ID, Asset2ID, Asset3ID, Asset4ID, MSCOA, CaseType, SOFTID, 
    OEMID, OEMDate_Of_Manufacturer, Grade, rScratch, rCracks, rColour, rCase, 
    FailureCode, Comments, LTPScreensize, CaseColour, CannotTest, BER, 
    ReDatawipedNotByBlannco, PATTest, PATTestRef, AccessoryBag, Track_Date, 
    InReceiving, InInspection, InTesting, InDatawipe, OnHold, InRepair, 
    OutRepair, InRefurb, InImaging, InStaging, InPackaging, InShipping, InReturned, 
    DateCompleted, Stockchecked, ImageName, ImageRev, ImageCreatedBy, OnHoldComments, 
    RepairMinutes, RepairComments, Rep_NoHDDRem, 
    Rep_HDDRemEmpName, Rep_HDDRemDateCert, Completed, OrderItemID, 
    OrderID, SaleStatus, Price, RFS, Sold, DateSold, DateDespatched, 
    BIOSUpdate, billref_logistics, billref_logistics_datetime, 
    billref_services, billref_services_datetime, billref_remarketing, billref_remarketing_datetime, 
    billref_other, billref_other_datetime, Deleted, StorageDT, RROrderCode, 
    billref_storage, billref_storage_datetime, updateFlag
    FROM         
    dbo.tbl_Units
    Where     (updateFlag IN (1, 2))
    GO
    
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  8. #8
    Join Date
    May 2007
    Posts
    49
    There are 4 more SPs like Trans_UnitBLSInsert in SSIS out of which 2 also join tables that are in another database.

    Ex. -

    Code:
    ALTER Procedure [dbo].[Trans_UnitProfileBLSInsert]
    As
    Begin
    
    
    	Select DISTINCT
    	vwTransferUnits.UID AS UnitID,
    	tbl_ActivityRequest.ID AS RequestId,
    	vwTransferUnits.TrackId AS TrackId,
    	vwTransferUnits.SerialId AS SerialId,
    	vwTransferUnits.Asset1ID as Asset1ID,
    	vwTransferUnits.Asset2ID as Asset2ID,
    	vwTransferUnits.Asset3ID as Asset3ID,
    	vwTransferUnits.Asset4ID as Asset4ID,
    	WebviewAssetManagement.dbo.TransfnGetHDD(vwTransferUnits.TrackId)   + ' (Asset Profile Data)'  AS BLSDisk_Disk,
    	WebviewAssetManagement.dbo.TransfnGetHDDSerial(vwTransferUnits.TrackId)   + ' (Asset Profile Data)' AS BLSDisk_Serial,
    	WebviewAssetManagement.dbo.TransfnGetHDDSize2(vwTransferUnits.TrackId)   + ' (Asset Profile Data)'  AS BLSDisk_Size,
    	WebviewAssetManagement.dbo.TransfnGetDisplaySerial(vwTransferUnits.TrackId)    + ' (Asset Profile Data)' AS BLSDisplay_Serial,
    	WebviewAssetManagement.dbo.TransfnGetFloppyDisks(vwTransferUnits.TrackId)  + ' (Asset Profile Data)' AS BLSFloppy_Product,
    	WebviewAssetManagement.dbo.TransfnGetFloppySerial(vwTransferUnits.TrackId)   + ' (Asset Profile Data)' AS BLSFloppy_Serial,
    	WebviewAssetManagement.dbo.TransfnGetOpticalSerial(vwTransferUnits.TrackId)   + ' (Asset Profile Data)' AS BLSOptical_Serial,
    	WebviewAssetManagement.dbo.TransfnGetOpticalMemory(vwTransferUnits.TrackId)   + ' (Asset Profile Data)' AS BLSOptical_Memory,
    	WebviewAssetManagement.dbo.TransfnGetOpticalSpeed(vwTransferUnits.TrackId)  + ' (Asset Profile Data)'  AS BLSOptical_Speed,
    	WebviewAssetManagement.dbo.TransfnGetProcessorName(vwTransferUnits.TrackId)    + ' (Asset Profile Data)' AS BLSProcessor_Name,
    	WebviewAssetManagement.dbo.TransfnGetProcessorSpeed(vwTransferUnits.TrackId)  + ' (Asset Profile Data)'  AS BLSProcessor_Speed,
    	WebviewAssetManagement.dbo.TransfnGetmb_chipset(vwTransferUnits.TrackId)  + ' (Asset Profile Data)'   AS BLS_MBChitSet,
    	WebviewAssetManagement.dbo.TransfnGetBLSProduct(vwTransferUnits.TrackId)  + ' (Asset Profile Data)'  AS BLS_Product,
    	tbl_BLS_Report.Product AS BLS_Product_Int,
    	tbl_BLS_Report.Created As BLS_Report_Created,
    	WebviewAssetManagement.dbo.TransfnGetMb(vwTransferUnits.TrackId)    + ' (Asset Profile Data)' AS BLS_Memory,
    	WebviewAssetManagement.dbo.TransfnGetBiosSerial(vwTransferUnits.TrackId)   + ' (Asset Profile Data)' AS BLS_BiosSerial,
    	WebviewAssetManagement.dbo.TransfnGetBiosMake(vwTransferUnits.TrackId)   + ' (Asset Profile Data)' AS BLS_BiosMake,
    	WebviewAssetManagement.dbo.TransfnGetBiosModel(vwTransferUnits.TrackId)   + ' (Asset Profile Data)' AS BLS_BiosModel,
    	WebviewAssetManagement.dbo.TransfnGetAssetTag(vwTransferUnits.TrackId)   + ' (Asset Profile Data)' AS BLS_AssetTag,
    	WebviewAssetManagement.dbo.TransfnGetNoHardDisks(vwTransferUnits.TrackId)  + ' (Asset Profile Data)'  AS BLS_NoHardDisks,
    	dbo.TransfnGetSnapShotSerialNo(vwTransferUnits.TrackId)  AS SnapShot_SerialId,
    	dbo.TransfnGetSnapshotAsset1ID(vwTransferUnits.TrackId) AS SnapShot_Asset1Id,
    	dbo.TransfnGetSnapshotAsset2ID(vwTransferUnits.TrackId) AS SnapShot_Asset2Id,
    	dbo.TransfnGetSnapshotAsset3ID(vwTransferUnits.TrackId) AS SnapShot_Asset3Id,
    	dbo.TransfnGetSnapshotAsset4ID(vwTransferUnits.TrackId) AS SnapShot_Asset4ID,
    	case
    		When tbl_UnitType.UnitType = 'Monitor' Then 
    			Case 
    			  When tbl_PartsMaster.CPUSpeedMonitor is not NULL then tbl_PartsMaster.CPUSpeedMonitor + ''
    			  Else tbl_PartsMaster.CPUSpeedMonitor
    			End	
    		When tbl_UnitType.UnitType = 'Laptop' Then 
    			Case
    			  When vwTransferUnits.LTPScreenSize is not NULL then  tbl_PartsMaster.CPUSpeedMonitor + ''
    			  Else tbl_PartsMaster.CPUSpeedMonitor
    			End
    	End  AS ScreenSize,
    	vwTransferUnits.deleted as deleted,
    	WebviewAssetManagement.dbo.TransfnGetBLSRef(vwTransferUnits.TrackId) + ' (Asset Profile Data)'  AS BLS_Reference,
    	WebviewAssetManagement.dbo.TransfnGetOpticalProduct(vwTransferUnits.TrackId)  + ' (Asset Profile Data)' as BLSOptical_Product,
        	WebviewAssetManagement.dbo.TransfnGetMACAddress(vwTransferUnits.TrackId) + ' (Asset Profile Data)'  As BLSReport_MAC,
    	WebviewAssetManagement.dbo.TransfnGetUnique_id(vwTransferUnits.TrackId)  + ' (Erasure Data)' AS BLSReport_UNIQUE_ID,
    	WebviewAssetManagement.dbo.TransfnGetCLNSECONDS(vwTransferUnits.TrackId)  + ' (Erasure Data)' AS BLSReport_CLEANER_SECONDS	
    	from vwTransferUnits
    	LEFT OUTER JOIN tbl_UnitType ON vwTransferUnits.UTCODE = tbl_UnitType.CODE
    	LEFT OUTER JOIN tbl_PartsMaster ON vwTransferUnits.OEMID = tbl_PartsMaster.ID 
    	LEFT OUTER JOIN tbl_ActivityRequest ON vwTransferUnits.ACTID = tbl_ActivityRequest.ActID 
    	LEFT OUTER JOIN WebviewAssetManagement.dbo.tbl_BLS_Report As tbl_BLS_Report ON tbl_BLS_Report.TrackID = vwTransferUnits.TrackID 
    	LEFT OUTER JOIN dbo.tbl_SnapshotUnits ON tbl_SnapshotUnits.TrackID = vwTransferUnits.TrackID
    
    End


    Functions -

    funcGetHDD
    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER OFF
    GO
    
    
    ALTER  Function [dbo].[funcGetHDD]
    (
    @strTrackID varchar(100)
    )
    RETURNS varchar(8000)
    AS
    BEGIN
    Declare @DiskVal varchar(8000)
    set @DiskVal = null
    
    SELECT @DiskVal = COALESCE(@DiskVal + ', ', '') + CAST([Disk] AS varchar(15))+' '
    			from tbl_BLS_Disk 
    			where TrackID=@strTrackID 
    
    Return @DiskVal
    END
    funcGetFloppyDisks
    Code:
    ALTER  Function [dbo].[funcGetFloppyDisks]
    (
    @strTrackID varchar(100)
    )
    RETURNS varchar(8000)
    AS
    BEGIN
    Declare @BLSvalues varchar(8000)
    set @BLSvalues = null
    
    	begin
    		SELECT @BLSvalues = COALESCE(@BLSvalues + '+', '') + ' '  +tbl_BLS_Floppy.product +' '
    		from  tbl_BLS_Floppy
    		where TrackID=@strTrackID 
    	end
    
    Return @BLSvalues
    END
    funcGetFloppySerial
    Code:
    ALTER  Function [dbo].[funcGetFloppySerial]
    (
    @strTrackID varchar(100)
    )
    RETURNS varchar(8000)
    AS
    BEGIN
    Declare @BLSvalues varchar(8000)
    set @BLSvalues = null
    
    	begin
    		SELECT @BLSvalues = COALESCE(@BLSvalues + '+', '') + ' '  +cast(tbl_BLS_Floppy.Serial as varchar(100))+' '
    		from  tbl_BLS_Floppy
    		where TrackID=@strTrackID 
    	end
    
    Return @BLSvalues
    END
    All functions returns single value made up of 3-4 concatinated values in 99% cases.
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    Can you post the FULL DML for tbl_BLS_Report please?
    Duh! I meant DDL!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    May 2007
    Posts
    49
    DDL is big, so attaching file.
    Attached Files Attached Files
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Version of SQL?

    How come you have a fill factor of 80% on an ever increasing clustered index? Are there lots of updates on the table?

    How come ALL text fields are NVARCHAR rather than VARCHAR? Was this upgraded from Access (lots of 255 character fields)? Why are all numeric fields INT rather than mixtures of TINYINT, SMALLINT, INT etc?

    could you check that this seeks, and, if so, how expensive the lookups are:
    Code:
      SELECT CAST(tbl_BLS_HDD.HDDSize AS VARCHAR(20)) 
      FROM  tbl_BLS_Report
      WHERE TrackID=@strTrackID
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    May 2007
    Posts
    49
    yes, database heavily accessed everyday with lot of insert / update operations. All fields are of NVARCHAR type to provide multi-language support and it is not upgraded from Access. I am not sure about why many fields are of type INT instead of mixtures of TINYINT, SMALLINT, INT; it is designed by someone else.

    Sorry but I am not sure about "could you check that this seeks, and, if so, how expensive the lookups are",

    Code:
    StmtText                                                                                                                                                                                                               StmtId      NodeId      Parent      PhysicalOp                     LogicalOp                      Argument                                                                                                                                                                                         DefinedValues                                                            EstimateRows  EstimateIO    EstimateCPU   AvgRowSize  TotalSubtreeCost OutputList                             Warnings Type                                                             Parallel EstimateExecutions
    ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------ ------------- ------------- ------------- ----------- ---------------- -------------------------------------- -------- ---------------------------------------------------------------- -------- ------------------
    
    
    SELECT CAST(tbl_BLS_Disk.[size] AS VARCHAR(20)) 
      FROM  tbl_BLS_Disk
      WHERE TrackID=' B85wph5a'                                                                                                                1           1           0           NULL                           NULL                           1                                                                                                                                                                                                NULL                                                                     1.012048      NULL          NULL          NULL        0.003283215      NULL                                   NULL     SELECT                                                           0        NULL
      |--Compute Scalar(DEFINE:([Expr1003]=CONVERT(varchar(20),[Webview].[dbo].[tbl_BLS_Disk].[size],0)))                                                                                                                  1           2           1           Compute Scalar                 Compute Scalar                 DEFINE:([Expr1003]=CONVERT(varchar(20),[Webview].[dbo].[tbl_BLS_Disk].[size],0))                                                                                                                 [Expr1003]=CONVERT(varchar(20),[Webview].[dbo].[tbl_BLS_Disk].[size],0)  1.012048      0             1.012048E-07  18          0.003283215      [Expr1003]                             NULL     PLAN_ROW                                                         0        1
           |--Index Seek(OBJECT:([Webview].[dbo].[tbl_BLS_Disk].[_dta_index_tbl_BLS_Disk_7_1818541612__K3_K22]), SEEK:([Webview].[dbo].[tbl_BLS_Disk].[TrackID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)  1           3           2           Index Seek                     Index Seek                     OBJECT:([Webview].[dbo].[tbl_BLS_Disk].[_dta_index_tbl_BLS_Disk_7_1818541612__K3_K22]), SEEK:([Webview].[dbo].[tbl_BLS_Disk].[TrackID]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD  [Webview].[dbo].[tbl_BLS_Disk].[size]                                    1.012048      0.003125      0.0001581133  15          0.003283113      [Webview].[dbo].[tbl_BLS_Disk].[size]  NULL     PLAN_ROW                                                         0        1
    is this what you need? or can you please guide me?
    I am also attaching screen shot of actual execution plan.
    Attached Thumbnails Attached Thumbnails Execution_Plan.gif  
    Mihir Mehendale
    DB Team @ Clarion
    Clarion Technologies
    (SEI CMMI Level 3)
    Pune, India
    www.clariontechnologies.co.in
    www.vEmployee.com

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It's the updates that are important regarding my point - not the inserts.

    That execution plan you posted is perfect.

    Minor, but don't need this index.
    Code:
    IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[tbl_BLS_Disk]') AND name = N'IX_TrackID_tbl_BLS_Disk')
    CREATE NONCLUSTERED INDEX [IX_TrackID_tbl_BLS_Disk] ON [dbo].[tbl_BLS_Disk] 
    (
        [TrackID] ASC
    ) ON [PRIMARY]
    GO
    The function you posted in #3 selects from a different table to the code you posted. The indexes for the table in #3 are not ideal for the query. How come you changed the table?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by mihirclarion
    I am not sure about why many fields are of type INT instead of mixtures of TINYINT, SMALLINT, INT; it is designed by someone else.
    I would write queries to get the upper and lower values of these and see if you can use a more appropriate data type - each time you can do this you will increase data storage efficiency.

    Similarly I would review your NVARCHAR columns to see if there are any that don't need to support a UNICODE character set.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yes - and waht version SQL Server please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •