Database

1-Mar-10 9:22

If I do something like:

Update Field1=something1, Field2=something2
Where Field3=123;

I always index Field3, but I'm not sure if I need to index Field1 and Field2 too?

Thanks!

Luc Pattyn1-Mar-10 9:34

Luc Pattyn

1-Mar-10 9:34

AFAIK you're query is not selecting based on field1 or field2, so it would not benefit from indexing those fields. Future queries obviously may or may not benefit from indexing.

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
All Toronto weekends should be extremely wet until we get it automated in regular forums, not just QA.

Mike6543211-Mar-10 9:42

1-Mar-10 9:42

Thank you. Just double checking.

David Mujica1-Mar-10 10:20

David Mujica

1-Mar-10 10:20

The other argument is that if you indexed field1 & field2, you would require more data writes. Both the main table data would be updated and the index data would be updated. Even worse, because you update data that is indexed you could cause all kinds of index reorganization when the value of field1 changes from A to Z.

My 2 cents.
Smile | :)

Mike6543211-Mar-10 20:30

Looking for a book on SSIS

1-Mar-10 20:30

That makes sense. Good info, ty.

Corporal Agarn1-Mar-10 6:30

Corporal Agarn

1-Mar-10 6:30

Hello,
I need to learn about SSIS (Microsoft SQL Server Integration Services) interface with .NET and stored procedures. I am looking for a book to learn from but also use as a reference book. I have Knight's 24-Hour Trainer but it is not exactly what I want.

Thanks
djj

Concatenating strings in MS SQL

#Jet1-Mar-10 1:05

#Jet

1-Mar-10 1:05

Hello,
i need help for a stored procedure that allows me to concat string fields from a table column when they have the same field names.

i did it when the fields were int and it worked perfectly

code snippet.

SELECT    X.FieldName,SUM(CAST(Y.Value AS float))
FROM      L INNER JOIN
                      C ON L.Id = C.Id INNER JOIN
                      B ON L.Id = B.LocalityId INNER JOIN
                      FormInstances ON B.FormInstanceId = FormInstances.Id INNER JOIN
                      Y ON FormInstances.Id = Y.FormInstanceId INNER JOIN
                      X ON Y.FieldId = X.Id
WHERE     (C.AId = @aid) AND (FormInstances.FormId = 6)
GROUP BY X.FieldName

this got me the sum of each field in Y based on X.fieldname.
now i want to do the same when the values in Y are not texts and not numbers. i tried conctenation(+) but was not successful with.

thanks in advance.

Re: Concatenating strings in MS SQL

Corporal Agarn1-Mar-10 6:42

Corporal Agarn

1-Mar-10 6:42

I do not understand what you want. However keep in mind that if you concatenate a null value the result will be null.
'John' + NULL + 'Doe' = NULL
Try using the ISNULL function.
Also you cannot mix types
'John' + 1.0
will give an error. You need to make all compatible types.

Good Luck
djj

Re: Concatenating strings in MS SQL

#Jet2-Mar-10 6:22

#Jet

2-Mar-10 6:22

Thanks so much for the reply.

1.all the data types are verified to be strings(text)

2. i want to concatenate the strings by grouping them on a fieldid.
when i pick an id i want to concat all values corresponding to the id.
(this is the structure.

i have table A which contains list of towns.
table B contains list of regions.
table C contains list of attributes of the towns
table D contains the values of the attributes

the task is to provide a summary for towns under a region. grouped
under the various attributes.

for Region A i have Attribute A: then the concatenated values for
all attributes A for each town.

thanks once again.

Re: Concatenating strings in MS SQL

Corporal Agarn3-Mar-10 0:54

Corporal Agarn

3-Mar-10 0:54

Do you really want to concatenate columns or just create a table?

-- Table
SELECT A.Town, B.Region, C.Attribute AS AttribCode, D.Attribute 
FROM TableA A 
INNER JOIN TableB B 
ON A.ID = B.ID 
INNER JOIN TableC C ........

--or 
-- Concatenate
SELECT A.Town + ISNULL(B.Region, '') + ISNULL(C.Attribute, '') + ISNULL(D.Attribute, '') AS TownInfo
FROM TableA A 
INNER JOIN TableB B 
ON A.ID = B.ID 
INNER JOIN TableC C ........

If you have a one to many relationship and want the say multiple attributes for a given town you will need to Google that as it has been covered in various places.

I hope this helps,
djj

ORM with streaming

pebrian2728-Feb-10 23:01

pebrian27

28-Feb-10 23:01

Hi! Is there an ORM that provides streaming support? What I mean by streaming is that the users can see the rows/objects being loaded one-by-one/partially (Think of SQL Management Studio when the query result is thousands of record). I can't implement paging since our data changes very fast.

Background:
- DB contains >250 million of data (but I will limit the result set to 10K max)
- Query can take minutes even hours
- Can't change DB architecture

Thanks

Mycroft Holmes28-Feb-10 23:55

Mycroft Holmes

28-Feb-10 23:55

pebrian27 wrote:
but I will limit the result set to 10K max

Why, how useful is 10k records in a UI, why not limit to 100 or 50 or something the user can work with.

pebrian27 wrote:
Query can take minutes even hours

This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.

If the query takes hours the user has got bored and gone home. Change you design to data dump, output the data via a job/SSIS package and make it available in a shared folder. We extended SSRS to do just this sort of work, anything more than 1k rows is a lousy design for a UI and a report.

[edit] Checked your profile, web developer, so use an FTP/download scenario[/edit]

Never underestimate the power of human stupidity
RAH

pebrian271-Mar-10 0:26

pebrian27

1-Mar-10 0:26

Our users are also technical people and they need ~10k records.
Also, the users get to create the queries (ad-hoc queries). My current ORM is Nhibernate so they use HQL which is very similar to SQL.

Mycroft Holmes wrote:
pebrian27 wrote:
Query can take minutes even hours
This does not make sense when servicing a UI. A select statement that takes hours, ridiculous, your design is screwed.

Oops... Queries will only take hours if I don't do the 10k limit. Currently, querying 500 records takes 2 mins. Thats why I want streaming support so that if they get bored, they can press cancel but still get partial result.

Our systems will be upgraded after 8 months.

pebrian271-Mar-10 0:45

pebrian27

1-Mar-10 0:45

Nhibernate isn't the cause of the slow query and there are no joins in the queries

Sorry in advanced for this brain dead question

i.j.russell1-Mar-10 1:20

i.j.russell

1-Mar-10 1:20

500 records in 2 mins with no joins is very very slow. It would appear that either your server is useless or the query optimizer is unable to use indexes and is doing a table scan of all 250million records. Have you looked at the query execution plan to see if there are indexes that you can add?

Mike65432128-Feb-10 13:17

28-Feb-10 13:17

This is probably easy...I am just brain dead...I am using Ms Sql 2008, and given the following two tables:

Table1
---------------
Field1 Field2
3 55
3 55
4 55
5 55
3 66
3 66
3 77
4 77
3 88

Table2
---------------
Field1 Field2
3 9
4 10
5 11

Table1 and Table2 are linked together by the Field1's...I get a parameter passed in like "9" for Table2's Field2...how do I return all of the records from Table1 of just the 66's and the one record of 88?

Not Active28-Feb-10 13:31

Not Active

28-Feb-10 13:31

SELECT Field2 FROM TABLE1<br />
WHERE Field1 = (SELECT Field1 FROM Table2 WHERE Field2 = 9)<br />
AND Field2 IN(66,88)

I know the language. I've read a book. - _Madmatt

Mike65432128-Feb-10 13:36

28-Feb-10 13:36

Thank you, but I don't want to hard code the 66 and 88 as those values I constantly changing. I just wanted those records as they don't have other linking fields of the 4 and 5 for example.

Not Active28-Feb-10 13:52

Not Active

28-Feb-10 13:52

Of course you don't want them hard coded, this was just an example. Roll eyes | :rolleyes:

I know the language. I've read a book. - _Madmatt

Tim Carmichael28-Feb-10 13:45

Tim Carmichael

28-Feb-10 13:45

Start with:

select t1.*
from t1 inner join t2 on t1.field1 = t2.field1
where t2.field2 = @passedvalue

But, why is the (3,55) pair excluded?

Tim

Mike65432128-Feb-10 13:48