Results 1 to 12 of 12

Thread: [RESOLVED] I need advice on performance and use of "redundant" data SQL Server Database

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

    Resolved [RESOLVED] I need advice on performance and use of "redundant" data SQL Server Database

    We have a form in our application that does an "equipment" search. Equipment is items that we sell to a customer. WHen the form loads, it loads all equipment records (135,475 currently) into a table adapter for display in a grid. Then there are criteria a user can enter. The first criterion defaults to the customer, so a subset of those 135k records is displayed in the grid immediately. If the user is looking for a certain serial number they can start typing in a textbox and the grid is dynamically built to match the user's new criteria on demand. (I think that's the right phrase, meaning the user types and the grid automatically repopulates matching his criteria one keystroke at a time). Other criteria are Make and Model, etc. which are in dropdownlists.

    Then management wanted an enhancement made to the grid to display a Received Date and an Invoiced Date. So there are two new columns I have to get data for. The data come from tables not yet in the original query. Because of complicated logic, I figured I would leave the query alone and get the data for the new columns in my C# code. I am using DevExpress controls; I am not sure how other grids would work, but the columns are unbound data, so as each row is processed code executes to go get data for the new columns. This is taking a long time, because I have some column data in the table adapter and then I have to get more data one row at a time. And each user keystroke is firing my routine one keystroke at a time until the user is done typing his critiera. Ugh.

    So what are my options? I am wondering if I should store the data I need in a database table intead of trying to query it as needed. For example, Invoiced Date. This is the date a job was marked CP by the user; the job being when the equipment was ordered and arrived and there was nothing further for us to do so it's complete. How I am doing this now is...

    Code:
    SELECT        chg.EnteredOn
    FROM            xtblSubJob AS subJob INNER JOIN
                             Jobs ON Jobs.Control = subJob.relJobControl LEFT OUTER JOIN
                             xtblJobChanges AS chg ON chg.JobControl = Jobs.Control AND (chg.TopicUpdated = 'CP' OR
                             chg.TopicUpdated = 'ACP')
    WHERE        (subJob.SerialNumber = @serialNumber) AND (subJob.SaleUnitType = @saleUnitType) AND (Jobs.Status = 'CP')
    xtblSubJob has the serial number. That table points to the job/case in a many to one relatinoship. Then we have an audit table that writes records based on user activity so I am looking for the when the user marked the job CP complete or ACP all complete. And saleUnitType figures into it because we don't what the InvoicedDate for all equipment, just certain kinds of sales.

    Management here likes redundant data. I don't. So i was happy when I found a way to go get data without creating the same data in another table. But now I'm thinking I should run a bulk update once and just have the data immediately available in one of the tables already being selected from in the original query that's speedy. Then add code that for future transactions writes the Invoiced Date to the table. You can never update an invoiced date, so it's not like that would ever change causing my duplicated data to be different.

    If you are still with me here...what are your thoughts? Am I worrying about the right things? Is my redundant data solution the best solution for this scenario?

    Thank you!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,246

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    The first thing you should do is grab all of the necessary data with a new query, as complicated as it needs to be, and see what the performance is like. Doing a row-by-row retrieval of dates as you are doing now via code is silly.

  3. #3
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,590

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    Quote Originally Posted by OptionBase1 View Post
    The first thing you should do is grab all of the necessary data with a new query, as complicated as it needs to be, and see what the performance is like. Doing a row-by-row retrieval of dates as you are doing now via code is silly.
    This was my thought... create a new view or stored proc (depending on what the original query was and if it has paramers (sproc) or not (view)) that extends the original query (so that it doesn't change) and adds the new data cols you need.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    OK. just realize my C# skills are way ahead of my database skills. I may be posting back for questions on how to write the view. But it you say that's the way to go, I'm in!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    Thanks for your help. I learned about CROSS APPLY. And then OUTER APPLY. Also, it turns out I did not need to use the table I clipped into post #1; there was a more straightforward way of getting InvoicedDate. It was DateReceived (first column selected) that was tricky.
    Code:
    CREATE VIEW [dbo].[xqryEquipmentListDatesReceivedAndInvoiced]
    AS
    select 
    	CASE 
    	WHEN SaleUnitType = 1 THEN subJob.EnteredOn 
    
    	WHEN (SaleUnitType = 2 or SaleUnitType = 4) THEN 
    
    		
    		(SELECT  
    		 CASE 
    			WHEN ma.ReceivedShipDate IS NULL 
    			THEN it.ReceivedDate 
    			ELSE ma.ReceivedShipDate 
    		 END 
    		FROM xtblMROrderMaster AS ma 
    		LEFT OUTER JOIN xtblMROrderSerialNumbers AS SN ON SN.MROrderMasterControl = ma.Control 
    		LEFT OUTER JOIN xtblMROrderItems AS it ON it.MROrderMasterControl = ma.Control AND it.BoxNumber = SN.BoxNumber 
    		WHERE  SN.SerialNumber = tblEquipment.serialNumber)
    
    	ELSE '01/01/1753'
    	END AS DateReceived,
    
    -- Yes, this is all it has to be...
    	(SELECT Jobs.P21SyncDate FROM Jobs WHERE Control = subjob.relJobControl) AS InvoicedDate,
    
    	subJob.SubJobControl,
    	
    	tblEquipment.EquipmentControl, 
    	tblEquipment.PurchasedSO, 
    	tblEquipment.PurchasedDate, 
    	tblEquipment.Make, 
    	tblEquipment.Class, 
    	tblEquipment.SerialNumber, 
    	tblEquipment.Description, 
    	tblEquipment.SiteControl, 
    	tblSites.SiteName,
    	tblEquipment.Retired, 
    	tblEquipment.OwnerID, 
    	tblEquipment.ASN1, 
        tblEquipment.ASN2,
    	tblEquipment.bReturnedToSupplier,
    	tblEquipment.Unrepairable, 
    	tblEquipment.EquipmentMissing, 
    	tblEquipment.Type
    
    FROM P21.dbo.p21_view_customer 
    RIGHT OUTER JOIN dbo.tblEquipment ON P21.dbo.p21_view_customer.customer_id_string = dbo.tblEquipment.OwnerID 
    LEFT OUTER JOIN dbo.tblSites ON dbo.tblEquipment.SiteControl = dbo.tblSites.SiteControl
    outer apply 
    	 (select top (1) subJob.*
          from xtblSubJob subJob
          where subJob.UnitEquipmentControl = tblEquipment.EquipmentControl) subjob
    
    GO
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    Oh. I also added in ORDER BY SubJobControl to that select top(1). Because as I just posted it and looked at it again, I was wondering why I was randomly selecting some top record without saying which one I wanted! I was using this SO post for help. Which did not have an ORDER BY, for some reason...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    39,222

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    So....how's the performance?
    My usual boring signature: Nothing

  8. #8

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    LOL. Performance is a smidge faster than when I was doing it one at time, row by row, loop call database loop again
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  9. #9
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    39,222

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    So, faster, but not good enough from the sound of it.

    I can't say I've done quite what you are doing, in two regards. One is that I haven't used a TableAdapter for much, as I prefer the Datatable. Still, I doubt there's any difference between the two when it comes to filtering, or at least not enough to matter. The other is that, while I use DevExpress controls, I haven't tried filtering a grid in this fashion.

    At this point, I'd say that you need to do a bit of timing to try to figure out where the time is being spent. Timing the query should be pretty simple using a Stopwatch object. The cost of the query is a point where some SQL optimization might be warranted, but if the cost of the query is insignificant to the total cost of the sorting, then there isn't much point in looking at the SQL.

    Two other areas come to mind. I was thinking that I have only filtered smaller datasets than you are talking about. After all, I work with endangered fish, and when it comes to anything endangered....there often isn't all that much data. However, I realized that I actually AM filtering a dataset that is larger than yours, and it filters VERY fast. What I'm using is a DataView and filtering that DataView. It acts at a speed that is fast enough as to appear instantaneous, which is good enough. Is filtering a TableAdapter slower? Probably not. As a test, it might be worth timing the a selection from a DataView tied to your datatable. It might not be worth doing, but it might also give you some feel for how fast the upper limit would be.

    The other item is the DevExpress grid. I like that grid, and some people I work with insist on using it, so I have them in both web reports and desktop applications. In the web reports, they might filter a very large number of records, and are quite responsive...though it's web, so it doesn't really compare. The desktop uses are all for datasets so laughably small that they don't compare, either. Sorting a maximum of maybe ten to twenty rows just doesn't cause much strain.

    Still, DevExpress does some interesting things with their controls. That grid isn't like the DataGridView, which is just rows with a set of columns. The DevExpress data organization is more convoluted. I have seen a case where that did seem to impact things. That might be the reason to try making up a dummy grid based on a DataView or DataGridView (if you want to add a visual component to the test) and timing that. What I'd be trying to isolate is whether it is the filtering problem in general, or the filtering problem specific to the DevExpress grid, that is causing the slowdown.

    Still, I think if you do some profiling, you will find that it is the query, not the filtering, that is causing the slowdown.
    My usual boring signature: Nothing

  10. #10

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    sh - No, I am sorry. I was trying to come across as sarcastic that it is a smidge faster than going one at a time, row by row. It is considerably faster. Acceptably faster!
    Your time wasn't wasted posting that long response. It's helpful to know what to do in the future, and your thoughts about DX and using a table adapter.
    And interesting that you work with endangered fish, and yes sad that there is so little data because there are so few of any endangered species!
    I think I will mark this thread resolved!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  11. #11
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,300

    Re: I need advice on performance and use of "redundant" data SQL Server Database

    Is this topic about general db performance advice or about a specific query? If the latter where are the tables definition, the indexes, the keys -- anything. . .

    There is one good thing about MS Access -- it requires index on every foreign key column and there is a reason about that. They probably received one too many reports that joins are performing abysmally even on moderately sized tables with no index on the FK.

    cheers,
    </wqw>

  12. #12

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,497

    Re: [RESOLVED] I need advice on performance and use of "redundant" data SQL Server Da

    The topic was originally about a query I was running (no performance issues ) and how to get additional data. I was fetching the additional data in the C# code. I was thinking about creating redundant static data (because of what a SQL loser I am). OptionBase1 and tg told me to stop being a loser and do it the right way, modify the query. I did and performance was fine. Then sh gave suggestions on how to zero in on where performance issues are, when they exist. So I think I was able to spare everyone the gory details of indexes and keys and our tables and just get help on a higher level. Thanks to y'all for taking the time to chime in!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

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



Click Here to Expand Forum to Full Width