Database

29-Mar-10 18:32

Could I write this as a exists statement we use this quite often and I read it has very poor performance.

WHERE `cl_client_common`.`UniqueNumber-900` IN(5337, 4439, 4725, 4096, 8078, 5912, etc, etc)

We do not know what the values will be until the user selects the clients they want.
All of the examples I see are using a subquery for the EXISTS statement and the only way I can think to write that with a subquery would be to use an IN in the subquery.

Thank you any help is appreciated.

Humble Programmer

_Damian S_29-Mar-10 18:46

_Damian S_

29-Mar-10 18:46

If you are storing the list of numbers in a temp table or similar, you could use

WHERE `cl_client_common`.`UniqueNumber-900` IN (select NUMBER from TEMPTABLE)

or simply by doing an inner join to the temp table...

Otherwise, the way you are doing it will have to do!!

I don't have ADHD, I have ADOS... Attention Deficit oooh SHINY!!

If you like cars, check out the Booger Mobile blog | If you feel generous - make a donation to Camp Quality!!

Mycroft Holmes29-Mar-10 19:11

29-Mar-10 19:11

Learn to use the sql profiler, it will take you query and give you an execution plan, it may even suggest what fields need indexes to optomise your query, it will certinaly give you the cost of each process. You can then decide on the best approach.

As DM said you have lots of options, temp table, table variable, sub query. Use and in() or an inner join or even exists as suggested earlier. I'm not sure if the exists is a better solution, I would be tempted to go with the table variable on a large table os just a sub query as you have done. Depends on the size of the data sets and the indexing.

Never underestimate the power of human stupidity
RAH

PIEBALDconsult31-Mar-10 8:38

PIEBALDconsult

31-Mar-10 8:38

I generally prefer to use a join.
I caution against IN unless the values are hard-coded (which is likely a bad design in its own right).
I especially dislike subqueries in WHERE clauses.
I used to have to use them with Oracle, but I don't recall ever doing them with SQL Server.

programmervb.netc++31-Mar-10 9:28

Reusable DataGridView and TableAdapter components in Lookup Tables?

31-Mar-10 9:28

We are joining the tables.
The values are not really hard coded...
The user is presented with a grid with checkboxes they can choose one or as many "clients" as they want in the grid.
Then we use the primary key for the record on the grid to create the new sql statement.
So user selects primary key 1,2,3,4 then our IN statement will be `UniqueNumber IN (1,2,3,4)

Humble Programmer

Bert Edens29-Mar-10 11:38

Bert Edens

29-Mar-10 11:38

Greetings, all...
I'm using Visual Studio 2008 Express with C# and SQL Server 2008 backend. I'm wanting to design a lookup table form where the user can select the table from a list of items on the left (probably a TreeView with a node for each entry), then have the grid on the right reflect the contents of that table (Users, Config settings, Security, etc.). But I don't have to have a table adapter for each table that could be on the form and would like to re-use the component by setting the properties on the fly. Has anyone done this before?
As a corrolary to this, if the user double-clicks on an entry in the grid, I would like to pop-up a form to allow the user to edit that entry. Again, I would like to use a single form to do this, without having a different form and table adapter, binding source, etc., for each possible table.
Has anyone done this before and can offer any suggestions? Or would the code to do it all run-time outweigh what it would take to just have separate table adapters and forms for each possible lookup table?
Thanks in advance!

- Bert

Re: Reusable DataGridView and TableAdapter components in Lookup Tables?

Mycroft Holmes29-Mar-10 19:05

Re: Reusable DataGridView and TableAdapter components in Lookup Tables?

29-Mar-10 19:05

This belongs in the design and architecture forum, certainly not the database forum, this has absolutely nothing to do with database.

Having said that, this is our standard design. First you need to chuck out the tableadaptor concept, you are using the MS widget/wizard crap things. Do some research into Data Access Layer, it will change the way you manage your data.

I have a utility that takes a datatable and binds it to a datagridview, formats the DGV and the various columns based on the data types of the datatable.

I have a giant switch statement that reacts to the node click event, gets the currentview (enum of all the tables to be displayed) from the node and decides what object to get a datatable from. The datatable is then passed to the DGV loader utility.

Then on the double click event of the DGV I already know what the currentview is from the loader so I know what form to display based on the currentview. I have the ID of the record clicked on (first filed always has the ID and is not visible in the DGV) so I pass the ID to the form in the constructor and pop a dialog.

The dialog gets the record (or an empty form for add) and saves the record back to the database. It returns a dialogresult to the main form.

If the main form get an Ok result it reloads the datatable from the database and refreshes the DGV with the new record in it.

As I said bog standard do it every day UI.

Never underestimate the power of human stupidity
RAH

Bert Edens30-Mar-10 4:06

Bert Edens

30-Mar-10 4:06

Thank you for the quick reply. I apologize for the posting in the wrong location. This crossed over to several areas, so I wasn't sure where it fit best.

I'm admittedly new to working with .NET and data access, having done most of my Windows coding in Delphi recently. Time to shift some paradigms, but that's just fine with me. Smile | :)

Thanks again!

- Bert

Group ranking or row number

Lash2029-Mar-10 9:29

Lash20

29-Mar-10 9:29

Hi:

I have data that looks like this:

iYear RecordID Group
2010 1 A
2010 2 A
2010 3 A
2010 4 D
2010 5 D
2009 6 C
2009 7 C

but I need to rank or apply row numbering on the Groups sorted by the RecordID to make the data look like this:

iYear RecordID Group Group Ranking
2010 1 A 1
2010 2 A 1
2010 3 A 1
2010 4 D 2
2010 5 D 2
2009 6 C 3
2009 7 C 3

Any help would be greatly appreciated.

Thx

Re: Group ranking or row number

programmervb.netc++29-Mar-10 9:36

Re: Group ranking or row number

29-Mar-10 9:36

I would think you could use a case statement to accomplish that but that is probably not the best way.

Humble Programmer

Mycroft Holmes29-Mar-10 18:48

How do you handle Dev DB vs Production DB?

29-Mar-10 18:48

This will make interesting reading[^] for you. Also look into PARTITION and RANK, that will allow you to rank the records over the group

Never underestimate the power of human stupidity
RAH

programmervb.netc++29-Mar-10 9:22

Re: How do you handle Dev DB vs Production DB?

29-Mar-10 9:22

When I first started working at my job we maintained local DBs for development on our own machines...so there was alot of upkeep backing up production data restore etc.
Then we had a spare server laying around so we started to use it as the devDB.
Now the server is being used for other purposes so we are back to square one which takes alot of time.
Does anyone have any suggestions of a better way to do this?
FYI we are using mySQL.

Humble Programmer

PIEBALDconsult29-Mar-10 18:09

PIEBALDconsult

29-Mar-10 18:09

Development servers. Every time.

programmervb.netc++ wrote:
backing up production data restore

Never did that; always generated test data -- it's repeatable.

Re: How do you handle Dev DB vs Production DB?

programmervb.netc++29-Mar-10 18:35

Re: How do you handle Dev DB vs Production DB?

29-Mar-10 18:35

I would think it would take more time to generate the amount of test data we need than it would to do backup restore...
Are there tools for this?

Also one reason the data being REAL data matters is because we are converting all apps from Access to mySQL and VB6 to VB.NET so we are running our reports against old and new data to make sure we did not screw up a conversion or the new/better ways we are writing the SQL.

Humble Programmer

Mycroft Holmes29-Mar-10 18:54

29-Mar-10 18:54

3 Servers is a requirement, dev, uat and production. If/when we have a major deployment and can sync all the servers from production we do this. It does not happen very often as the development almost never stops so all the servers have different versions. But then we have something like 14 apps under production.

We also have another server for bug tracing, take a snapshot of the production server and chase down the problem.

We also use SQL compare and data compare to do deployment, greatest time save ever for deployment.

Never underestimate the power of human stupidity
RAH

Database design question

Alivemau529-Mar-10 4:00

29-Mar-10 4:00

I have a table of football matches, and each match can have 5 different statuses:
Not started, 1st halftime, Break, 2nd halftime, Ended

My question is, what is the correct way of modeling this?:

Matches (MatchId, Status, ...)
where Status is an integer or a string corresponding to each status

vs

Matches (MatchId, StatusId, ...)
where StatusId is a foreign key to MatchStatuses,
MatchStatuses (MatchStatusId, Description)

vs

Matches (MatchId, StatusId, ...)
where StatusId is a foreign key to MatchStatuses,
MatchStatuses (MatchStatusId, Description, IsNotStarted, IsFirstHT, IsBreak, IsSecondHT, IsEnded)

for example, a registry in this case would be: (1, "First Half time", false, true, false, false, false)

THANKS!

J4amieC29-Mar-10 4:56

J4amieC

29-Mar-10 4:56

I would go with 1 (ok solution) or 2 (better solution)

Solution 3 was just dumb to the max.

Alivemau529-Mar-10 5:01

29-Mar-10 5:01

With (1) you would have to hardcode any combobox (for example) containing the different possible entries.

With (2) you wouldn't know which one is each..

Diff between 2 and 3 would be that in the program itself you would have to use:

someMatch.Status.Finished == true
vs
someMatch.Status.Description == 'finished'

But im not convinced, though, of solution (3)

J4amieC29-Mar-10 5:17

J4amieC

29-Mar-10 5:17

Alivemau5 wrote:
With (2) you wouldn't know which one is each..

public enum MatchStatus
{
  NotStarted = 1,
  FirstHalf = 2,
  HalfTimeBreak = 3
  //.. etc
}

If you have code relying on this, the enumerate it in code.

Alivemau529-Mar-10 6:24

29-Mar-10 6:24

You have convinced me.

And for users privileges do you think its a good idea to have the following?:

Users (UserId, UserTypeId, ..)
UserTypes (UserTypeId, Name, MayAddMatch, MayDelMatch, May..., May.., May... )

with registries like (1, "Admin", true, true, true...)

(i'm not doing an offtopic.. its a similar question)

programmervb.netc++29-Mar-10 9:11

29-Mar-10 9:11

Alivemau5 wrote:
With (2) you wouldn't know which one is each..

You could know which one is which this is how we do it.
This may not be the cleanest way but it is very easy to maintain.
We create an integer array such as Dim blah() as Integer and set a default size for it close to what we think the number of records will be to minimize the use of redim.
Then do something like this with the record set you retrieve.

While myReader.Read
            cmbControl.Items.Add(myReader.GetString(1))
            lKeyIndex(lIndex) = myReader.GetValue(0)
            '
            lIndex += 1
            ' see if we need to redimension the array...
            If lIndex Mod iCurrentDim = 0 Then
                iCurrentDim = iCurrentDim + iDefaultDim
                ReDim Preserve lKeyIndex(iCurrentDim)
            End If
End While

Here is a example query.

SELECT `UniqueNumber`, `Description` FROM `ct_contsources_master` WHERE `Inactive` = 0 ORDER BY `Description`

Notice the Inactive flag...
If you use this than if a row is no longer valid you can set it to 1...therefore not populate the combo.

EDIT..
Oh yeah this will help..

Public Function fiSetComboIndex(ByVal lKey As Long, ByRef cboControl As ComboBox, ByRef lKeyIndex() As Long) As Integer
       ' this routine sets the current "SelectedIndex" to the lKey passed in...
       Dim iII As Integer
       Dim iKeyIndexLength As Integer = lKeyIndex.Length - 1
       '
       fiSetComboIndex = 0
       For iII = 0 To iKeyIndexLength
           If lKeyIndex(iII) = 0 And iII > 0 Then
               Exit For
           End If
           If lKeyIndex(iII) = lKey Then
               fiSetComboIndex = iII
               Exit For
           End If
       Next
   End Function

Use this to get the value that is selected.

blah(comboBoxName.SelectedIndex)

Store the result of that in the main table.

Humble Programmer

riced29-Mar-10 5:57

riced

29-Mar-10 5:57

Depends on what questions you want to answer.
If the only question you want to answer is 'What is the status of a match?' then first suggestion is good enough. This effectively means you have a one-one relation between matches and statuses.

If you want to answer questions like 'What were half-time and full-time scores of a match?' then you have a one-many relation between matches and statuses so you need to use your second suggestion.

Your third suggestion seems to offend first order normalization.

I'd go for the second approach because it's more flexible. This may also mean moving some columns from Matches into the statuses table (e.g. the score since you have both half and full time scores).

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Alivemau529-Mar-10 6:30

29-Mar-10 6:30

Well my second suggestion was more like a table with 5 registries than a table with n registries, that is:

(1, Not started)
(2, First Half)
(3, Break)
(4, Second half)
(5, Ended)

And no more columns.. not this:
(1, Not started)
...
(23, Not started)
I think you understood that

This second suggestion was thinking on that I could fill any later combo (for example) without hardcoding it.. but the enum solution works and I wouldnt need to hardcode.