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

[MMock]

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...

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!

[OptionBase1]

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.

[techgnome]

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

[MMock]

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!

[MMock]

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.

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

[MMock]

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...

[Shaggy Hiker]

So....how's the performance?

[MMock]

LOL. Performance is a smidge faster than when I was doing it one at time, row by row, loop call database loop again

[Shaggy Hiker]

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.

[MMock]

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!

[wqweto]

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>

[MMock]

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!