Showing posts with label indexed. Show all posts
Showing posts with label indexed. Show all posts

Friday, March 23, 2012

JOINS and Integers vs. Indexed "Strings".

I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient.What do you think?

TIA,

barkingdog

It is the size of the index key which is most important. The larger your index key is, the more pages should be processed (and probably read from the hard disk: this is the slowest operation) while executing query. And, of course, there are some overhead comparing to strings in terms of collation. So, integer field is more effective in most cases.

Wednesday, March 7, 2012

JOIN the only relationship method?

In the old days..with DbaseIII, if two tables were indexed on an ID field, a
t
the top of the program, I would "open" this relationship by using the right
syntax and "boom" ..it's done.. the record pointers are at the top of each
table waiting for a command.
In SQL, it appears the that JOIN statement is what sets up the
"relationship" only when I execute a join with a million records in each
table, it takes 15 seconds for the relationship to be "established". It
appears that during the JOIN, a scan takes place and all the records are in
both tables are "positioned" during a sequential scan of the main table.
Which wouldn't be so bad if it were a one time deal, but it's in a SP that
gets repeatedly called by a client application.
In Dbase III, because of the indexed fields, it knows to just positions the
record pointer at the top of each table..it doesn't have to scan through all
the tables "relating" all the records one by one each time the relationship
is established.
There has to be a quicker way to relate tables in these more modern
languages, but i'm not sure where to start looking.
Thanks,
Don
declare @.msym varchar(200)
declare @.mcat varchar(200)
declare @.msid varchar(200)
select @.msym = 'c'
select @.mcat = 'FINC'
select @.msid = 'Fly'
select distinct m.* from main m
left join sym2 s on s.id = m.id
left join cat2 c on c.id = m.id
left join servid2 i on i.id = m.id
The ID field in all 4 tables are INT and indexed.
The ID field in Main is unique. The ID fields in the other 3 fields are not
unique.The fact that data retrieval isn't constrained by pointers or
hierarchies in the database is a major advantage of relational
databases. SQL isn't truly relational but it does follow the model to
that extent.
Your real question should be "How do I optimize my queries?". But the
query you posted is a woeful example with which to tackle that
question. What's the point of retrieving the entire contents of the
tables in a single operation? Why have you used SELECT * instead of
specifying only the required columns? Why have you used DISTINCT? Very
likely this query will result in undesirable and slow scans and sorts -
but if this is your idea of how to write queries then you should
probably take a course before you do anything more. What are you
*really* trying to achieve?
David Portas
SQL Server MVP
--|||Here's the rest of the Select statement...you may be right, it may be a
design problem, i don't know.
where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
or i.servid in (select str from ter_charlist_to_table(@.msid, DEFAULT))
sym, cat and servid are tables with 2 fields...ID and a varchar field. The
ID field's are unique. The varchar field can have repeating values. for
example,
1001 DELL
1004 DELL
1007 DELL
What needs to be returned is all fields from the Main table if the request
is for DELL.
What I'm trying to do is have 1 main table "Main" with unique ID field..be
related to 3 other tables on ID where the ID in these tables is not unique.
There is a varchar field in Main that has values like this:
"DELL,IBM,WHATEVER"...
A client wants all fields returned from Main that have the word "DELL" in th
e
"David Portas" wrote:

> The fact that data retrieval isn't constrained by pointers or
> hierarchies in the database is a major advantage of relational
> databases. SQL isn't truly relational but it does follow the model to
> that extent.
> Your real question should be "How do I optimize my queries?". But the
> query you posted is a woeful example with which to tackle that
> question. What's the point of retrieving the entire contents of the
> tables in a single operation? Why have you used SELECT * instead of
> specifying only the required columns? Why have you used DISTINCT? Very
> likely this query will result in undesirable and slow scans and sorts -
> but if this is your idea of how to write queries then you should
> probably take a course before you do anything more. What are you
> *really* trying to achieve?
> --
> David Portas
> SQL Server MVP
> --
>|||> where s.sym in (Select str from iter_charlist_to_table(@.msym, DEFAULT) )
> or c.cat in (select str from iter_charlist_to_table(@.mcat, DEFAULT) )
> or i.servid in (select str from ter_charlist_to_table(@.msid, DEFAULT))
Populate a table with those values before you execute the statement. That
way you can ensure the function only gets called once.

> ere is a varchar field in Main that has values like this:
> "DELL,IBM,WHATEVER"...
Deliminted lists of values in columns are a fundamental weakness. If these
are a discrete known set of values then put them in a related table.
David Portas
SQL Server MVP
--

Monday, February 20, 2012

Join on agragate function between tables

A have a number of similar tables and what I want to do is to get the count of records grouped by day of week. All tables have date as an indexed unique column but the actual timestamps differs and have no relation. For one table I use this simple querry:

Select DatePart(dw,dato) AS DOW, Count(dato) AS NOR FROM AWP2
where dato > '2006-08-11'
Group By DatePart(dw,dato)

A typical result:

DOW NOR
3 8934
6 22397
7 23328
1 23401
4 1938
2 24399
5 1112

Trying to join two or more tables in all sorts of variants of this:

Select datePart(dw,a1.dato) AS DOW1,Count(a1.dato) [Amount 1],
datePart(dw,a2.dato) AS DOW2, Count(a2.dato) [Amount 2]
FROM AWP1 A1 Inner Join AWP2 A2 on datePart(dw,a1.dato) = datePart(dw,a2.dato)
Where a1.dato > '2006-08-11' AND a2.dato > '2006-08-11'
Group By datePart(dw,a1.dato), datePart(dw,a2.dato)

Here I get this as a typical result:

DOW1 Amount 1 DOW2 Amount 2
6 332953802 6 332953802
3 42248886 3 42248886
1 330281714 1 330281714
7 335759904 7 335759904
4 1232568 4 1232568
5 210168 5 210168
2 366985359 2 366985359

Where the numbers are way off.

Any suggestions?

When you Join the tables, based only on the day of the week, your resultset will have each record in Table1 for a day of the week combined with each record in Table2 for the same day of the week. That is why your numbers are so far off.

Here is a simple example that shows what's happening.

Drop Table Table1

Create Table Table1(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

Drop Table Table2

Create Table Table2(

pkid int not null Identity(1,1),

dato datetime not null,

valCol varchar(10)

)

insert Table1 values( '1/1/2006', 'Fred' )

insert Table1 values( '1/2/2006', 'Barney' )

insert Table2 values( '1/1/2006', 'Wilma' )

insert Table2 values( '1/1/2006', 'Pebbles' )

insert Table2 values( '1/2/2006', 'Betty' )

insert Table2 values( '1/2/2006', 'BamBam' )

Select DatePart(dw, dato ), Count(*)

From Table1

Group

By DatePart(dw, dato )

Select DatePart(dw, dato ), Count(*)

From Table2

Group

By DatePart(dw, dato )

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

t1.valCol,

t2.valCol

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

valCol valCol

-- -- - -

1 1 Fred Wilma

1 1 Fred Pebbles

2 2 Barney Betty

2 2 Barney BamBam

Select DatePart(dw, t1.dato ),

DatePart(dw, t2.dato),

Count(t1.dato),

Count(t2.dato)

From Table1 t1

Join Table2 t2

On DatePart(dw, t1.dato) = DatePart(dw, t2.dato )

Group

By DatePart(dw, t1.dato ),

DatePart(dw, t2.dato)

-- -- -- --
1 1 2 2
2 2 2 2
Select Coalesce( t1.dow, t2.dow ),

t1.howmany,

t2.howmany

From (

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table1

Group by DatePart(dw, dato )

) t1

Full Outer Join

(

Select DatePart(dw, dato ) dow,

Count(*) howmany

From Table2

Group by DatePart(dw, dato )

) t2

On t1.dow = t2.dow

howmany howmany
-- -- --
1 1 2
2 1 2

Select dayofweek,

(Select Count(*) from Table1 where DatePart(dw, dato ) = dayofweek ),

(Select Count(*) from Table2 where DatePart(dw, dato ) = dayofweek )

From (

Select 1 as dayofweek

Union All

Select 2 as dayofweek

Union All

Select 3 as dayofweek

Union All

Select 4 as dayofweek

Union All

Select 5 as dayofweek

Union All

Select 6 as dayofweek

Union All

Select 7 as dayofweek

)t1

dayofweek
-- -- --
1 1 2
2 1 2
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0

There are several ways to get the days of the week. If you know that one of the tables will have records for every day, you can use that. There is a system table that has lists of numbers, that is another good source.

|||

Excellent stuff. I allready guessed the reason for my results but no idea how to stop it.