problem with join

hi, I have two tables join with each other,
key from table1 is nvarchar column, key from table2 is varchar column, so
when they join, the performance is very bad, however, when I change both
them to varchar and join them together, the performance is much better.
but the problem is I don't want to change the column definition, I still
want to improve the query. is it possible?When column datatypes are different in comparison side, SQL Server has to
internally convert it for you and that's why your query is performing slower
.
This is by design and is the expected.
You can use CAST funtion and see if it helps which I highly doubt.
"Britney" wrote:

> hi, I have two tables join with each other,
> key from table1 is nvarchar column, key from table2 is varchar column, s
o
> when they join, the performance is very bad, however, when I change both
> them to varchar and join them together, the performance is much better.
> but the problem is I don't want to change the column definition, I still
> want to improve the query. is it possible?
>
>

Problem with instalation

I have a problem with SQL 2005 Express on Win XP, at the start of instalation, with

- Performance Monitor Counter Requirement (Error)

Messages

Performance Monitor Counter Requirement

The System Configuration Check for Performance Monitor counter registry value has failed. For details, see How to: Increment the Counter Registry Key for Setup in SQL Server 2005, in the readme file or in SQL Server Books Online

Please, send me correct values of this kezs, because I deleted them,

I'm going to move this to the Setup forum.

- Mike

|||

This can be a couple things, but typically it is the perflib registry key. Check out this article and see if it applies to your environment:

http://msdn2.microsoft.com/en-us/library/ms143215(SQL.90).aspx

One other thing to keep in mind. The registry key listed in the article HKLM\...\009 represents the English language code. If you are running a different language (or multiple languages), you will have another language code in the registry path (ex: 004 for Chinese Simplified). You will need to make the changes to both hives.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Perflib\009]

Thanks,
Samuel Lester (MSFT)

Problem with dbo.sysindexkeys in SQL 2000

Hello,

I am experiencing some performance issues with the dbo.sysindexkeys.

I wonder if anyone can help me create an index on it?

Thanx!

SQL script:

set nocount on
set ansi_warnings off
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create test database'
print
'--------------------------'

use master
go

if not db_id('test') is null
drop database test
go

create database test
go

use test
go

--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create 4000 tables in test database'
print
'--------------------------'

declare @.i_iter int

set @.i_iter = 0
while @.i_iter < 4000
begin

if @.i_iter % 100 = 0
print 'Table -> ' + cast(@.i_iter as char(10))

exec ('
create table TABLE_' + @.i_iter + '
( COLUMN_0 int not null,
COLUMN_1 int not null,
COLUMN_2 datetime not null,
COLUMN_3 datetime not null,
COLUMN_4 varchar(100) not null,
COLUMN_5 smallint not null,
COLUMN_6 bit not null,
constraint TABLE_' + @.i_iter + '_PK primary key (COLUMN_0,
COLUMN_1, COLUMN_2)
)
create index TABLE_' + @.i_iter + '_I1 on TABLE_' + @.i_iter + '
(COLUMN_4, COLUMN_5)
')

set @.i_iter = @.i_iter + 1
end
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Dump dbo.sysindexkeys in temp. table and create a clustered
index on it'
print
'--------------------------'

if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
select * into dbo.t_sink from dbo.sysindexkeys
alter table dbo.t_sink add constraint t_sink_pk primary key ([id] ,
[indid], [colid])
go

print
'--------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using this'
print ' temp table'
print
'--------------------------'

if not object_id('tempdb..#dump') is null
drop table #dump
go

declare @.time datetime
set @.time = getdate()

select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #dump
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.t_sink sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name

print 'Rows: ' + cast(@.@.rowcount as char(10))

set @.time = getdate() - @.time
print 'Time: ' + convert(char(25), @.time, 114)
go

if not object_id('dbo.t_sink') is null
drop table dbo.t_sink
go
--################################################## ##########################################
--################################################## ##########################################
print
'--------------------------'
print ' Create a pivot table with all columns in the indexes and
primary keys using the'
print ' system tables'
print
'--------------------------'

if not object_id('tempdb..#direct') is null
drop table #direct
go

declare @.time datetime
set @.time = getdate()

select sobj.name as table_name,
sind.name as index_name,
max(case sink.keyno when 1 then scol.name end) as c01,
max(case sink.keyno when 2 then scol.name end) as c02,
max(case sink.keyno when 3 then scol.name end) as c03,
max(case sink.keyno when 4 then scol.name end) as c04,
max(case sink.keyno when 5 then scol.name end) as c05,
max(case sink.keyno when 6 then scol.name end) as c06,
max(case sink.keyno when 7 then scol.name end) as c07,
max(case sink.keyno when 8 then scol.name end) as c08,
max(case sink.keyno when 9 then scol.name end) as c09
into #direct
from dbo.sysobjects sobj,
dbo.sysindexes sind,
dbo.sysindexkeys sink,
dbo.syscolumns scol
where sobj.xtype = 'u'
and sind.id = sobj.id
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
and sink.id = sobj.id
and sink.indid = sind.indid
and scol.id = sobj.id
and scol.colid = sink.colid
group by sobj.name,
sind.name
order by sobj.name,
sind.name

print 'Rows: ' + cast(@.@.rowcount as char(10))

set @.time = getdate() - @.time
print 'Time: ' + convert(char(25), @.time, 114)
goherman (herman@.hotmail.com) writes:
> I am experiencing some performance issues with the dbo.sysindexkeys.
> I wonder if anyone can help me create an index on it?

I guess you could if you change the configuration parameer "allow updates
to system tables", but I have no idea wether SQL Server would actually
look at the index.

Anyway, this query executes almosts as fast as your query with
the temp table:

select sobj.name as table_name,
sind.name as index_name,
index_col(sobj.name, sind.indid, 1) as c01,
index_col(sobj.name, sind.indid, 2) as c02,
index_col(sobj.name, sind.indid, 3) as c03,
index_col(sobj.name, sind.indid, 4) as c04,
index_col(sobj.name, sind.indid, 5) as c05,
index_col(sobj.name, sind.indid, 6) as c06,
index_col(sobj.name, sind.indid, 7) as c07,
index_col(sobj.name, sind.indid, 8) as c08,
index_col(sobj.name, sind.indid, 9) as c09
--into #direct
from dbo.sysobjects sobj
join dbo.sysindexes sind ON sind.id = sobj.id
where sobj.xtype = 'U'
and indexproperty(sind.id, sind.name, 'IsAutoStatistics') = 0
and indexproperty(sind.id, sind.name, 'IsStatistics') = 0
order by sobj.name, sind.name

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Problem with dates

Hello,

I am trying to insert some date values into a table. I am using ODBC
prepared statements to (potentially) improve performance. The
statement syntax I am using is this:

INSERT INTO date_tests (date_col) VALUES ( CONVERT(DATETIME, ?, 121) )

The table is defined as "CREATE TABLE date_tests (date_col DATETIME)".
Then, I call this prepared statement in a loop to insert some date
values from an array. The values in the array look like this:
"1994-05-14 11:00:00:000" They are all valid dates and conform to the
ODBC date style supported by Sqlserver.

But I get the following error: [Microsoft][ODBC SQL Server
Driver]Optional feature not implemented.

I have tried different date formats and date values; but each time I
get the same error message. What is interesting is that if I use
straight "INSERT INTO " statements and not use a prepared statement,
there is no problem.

Will appreciate any suggestions and ideas to resolve this.Hi

Using the style parameter in the CONVERT function when converting to
datatime is not a documented option.

From BOL:
style

Is the style of date format used to convert datetime or smalldatetime data
to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data
types), or the string format when converting float, real, money, or
smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or
nvarchar data types).

John

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407121442.70ceb5ae@.posting.google.c om...
> Hello,
> I am trying to insert some date values into a table. I am using ODBC
> prepared statements to (potentially) improve performance. The
> statement syntax I am using is this:
> INSERT INTO date_tests (date_col) VALUES ( CONVERT(DATETIME, ?, 121) )
> The table is defined as "CREATE TABLE date_tests (date_col DATETIME)".
> Then, I call this prepared statement in a loop to insert some date
> values from an array. The values in the array look like this:
> "1994-05-14 11:00:00:000" They are all valid dates and conform to the
> ODBC date style supported by Sqlserver.
> But I get the following error: [Microsoft][ODBC SQL Server
> Driver]Optional feature not implemented.
> I have tried different date formats and date values; but each time I
> get the same error message. What is interesting is that if I use
> straight "INSERT INTO " statements and not use a prepared statement,
> there is no problem.
> Will appreciate any suggestions and ideas to resolve this.