I/O Read/Write Performance Problem

We have an ETL job occasionally encounters performance issue. Today, for
example, it creates several locks in SQL Server, and its I/O Read Bytes (from
Task Manager) are > 3,746,000,000,000 and I/O Write Bytes are >
2,268,000,000,000 and still increasing. The ETL has run for over 4 hours (as
opposed to 40 minutes usually). If the similar scenario happens to you, what
would you do immediately?
I am thinking of implemeting table hints when reviewing the queries in it,
what else can we further improve for long term solutions?
Hi,
I would suggest that you place a Sql Trace and a Performance monitor on the
job to determine why it is taking so long. There could be a multitude of
reasons that the job is taking so long from an improperly joined table to a
disk failure in the RAID array. I have posted a couple of links to help you
set up the perfmon and the profiler to aid in your diagnosis.
Hope this helps.
http://support.microsoft.com/default...b;EN-US;224453
http://support.microsoft.com/default...;en-us;Q224587
http://support.microsoft.com/default...;en-us;Q243589
sql

I/O Read/Write Performance Problem

We have an ETL job occasionally encounters performance issue. Today, for
example, it creates several locks in SQL Server, and its I/O Read Bytes (from
Task Manager) are > 3,746,000,000,000 and I/O Write Bytes are >
2,268,000,000,000 and still increasing. The ETL has run for over 4 hours (as
opposed to 40 minutes usually). If the similar scenario happens to you, what
would you do immediately?
I am thinking of implemeting table hints when reviewing the queries in it,
what else can we further improve for long term solutions?Hi,
I would suggest that you place a Sql Trace and a Performance monitor on the
job to determine why it is taking so long. There could be a multitude of
reasons that the job is taking so long from an improperly joined table to a
disk failure in the RAID array. I have posted a couple of links to help you
set up the perfmon and the profiler to aid in your diagnosis.
Hope this helps.
--
http://support.microsoft.com/default.aspx?scid=kb;EN-US;224453
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q224587
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q243589

I want write a Trigger that make a 3nd table that contain all the record

Hi
I want to write Trigger tha do this:
I have 2 table

main table & sub main table that have data like this
for example I have Bank (code 001) in main table and visa(code 0001) & mastercard(code 0002) in submain table.

or I have BMW(code 101) in main table and X5(code 0001) & X3(code 0001) in sub main table.

I want write a Trigger that make a 3nd table that contain all the record of that two table like this:

NAME CODE
Bank 001
visa card 0010001
master card 0010002

BMW 101
X5 1010001
X3 1010002
..........................................

Trigger MUST make code for any record ( main code * 1000 + submain code ) that is unic. and record name.

1) Set a foreign key in the "submain" table to reference the unique 3-digit codes in the main table.

2) Write an INSERT tigger on the "submain" table that inserts a JOIN of main+submain into the 3rd table.

Something like what you see below. You could also use a computed column as explained in the other post.

Thanks

set nocount on
go

use tempdb
go

create table main(
the_name char(6) not null primary key
, the_main_code char(3) not null constraint main_unique unique
)
go

insert into main values ('Banks', '001')
insert into main values ('Goods', '101')
go

create table submain(
the_name varchar(20) not null primary key
, the_submain_code char(4) not null
, the_main_code char(3) foreign key references main(the_main_code)
)
go

create table combined (
the_main_name char(6)
, the_submain_name varchar(20)
, the_combined_code char(7)
)
go

create trigger make_combined on submain
for insert
as
insert into combined
select main.the_name, inserted.the_name, inserted.the_main_code + inserted.the_submain_code
from main join inserted
on main.the_main_code = inserted.the_main_code
go

insert into submain (the_name, the_submain_code, the_main_code) values ('visa card', '0001', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('master card', '0002', '001')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X5', '0001', '101')
insert into submain (the_name, the_submain_code, the_main_code) values ('BMW X3', '0002', '101')
go

select * from combined order by the_combined_code
go

drop table submain
go
drop table main
go
drop table combined
go

I want to write an SQL statement which returns matching values but ignores the first 2 dig

I want to write a statement something like this

SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = 11/11/1234/)

But i want the search to ignore the first 2 digits so that it will return e.g

10/11/1234, 09/11/1234 so that it's only matching the last part

Any Help Would be greatly appreciated Thanks

try use Substring in SQL statement|||

I'm not sure if you want this to just a straight up query or something dynamic

If you want it to be passed into a query, you can do
string criteria = "11/11/1234";
"SELECT ... (File_No = " + criteria.Remove( 0, 2) + ");

Use Parameterized query and not the exact example above.

If you want just a straight up query use LIKE
SELECT... (File_No LIKE '%/11/1234')

|||

You can use something like this:

DECLARE

@.lcModifiedIDvarchar(10),

@.liMaxFiedlLength

asint,

@.liStartPoint

asint

SET

@.liMaxFiedlLength= 100

SET

@.liStartPoint= 4

SET

@.lcModifiedID=substring('11/11/1234/',@.liStartPoint,@.liMaxFiedlLength)

print

@.lcModifiedID

SELECT

Add_Date, File_NoFROM(SELECT'12/11/1234/' File_No,'aa'Add_date)aaWHEREsubstring(File_No,@.liStartPoint,@.liMaxFiedlLength)= @.lcModifiedID

Thanks

JPazgier

|||

I Have a textBox named TextFile which is where the user enters the file number which will be in the format of 11/11/1234 and then there is a button with an on click event to trigger my SQL query

I have an SqlDataAdapter with the first parameter set as @.FileNo

I want the query based entirely on the the value of textBox

I am currently doing it like this

SELECT Add_Date, File_No FROM dbo.File_Storage WHERE (File_No = @.File_No)

But this only returns exact matches and as i say i need to return values that ignore the first 2 digits contained in @.File_No

Thanks for such a quick response

|||

I am pretty sure you can change "File_No = @.File_No" to "File_No LIKE @.File_No"

Then when you declare the value of the parameter:
.Value = "%" + TextFile.Text.Remove( 0, 2);

|||

Yes, you are right maybe it will work but remember that LIKE structure is very slow and designed for another purposes.

Thanks

JPazgier

I want to write a query return particular dates of the previous we

I want to write a query return particular dates of the previous week. Is
there some function where I can pass the weeknumber (I think last week was
37?) and get it to return these dates, regardless of what day of the week I
run the query?
Mon,9/11/06
Tues, 9/12/06
Wed, 9/13/06
Thurs, 9/14/06
Fri, 9/15/06SELECT
Column1
, Column2
, etc
FROM MyTable
WHERE datepart( wk, MyDateColumn ) = 37
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>I want to write a query return particular dates of the previous week. Is
> there some function where I can pass the weeknumber (I think last week was
> 37?) and get it to return these dates, regardless of what day of the week
> I
> run the query?
> Mon,9/11/06
> Tues, 9/12/06
> Wed, 9/13/06
> Thurs, 9/14/06
> Fri, 9/15/06
>sql

I want to write a query return particular dates of the previous we

I want to write a query return particular dates of the previous week. Is
there some function where I can pass the weeknumber (I think last week was
37?) and get it to return these dates, regardless of what day of the week I
run the query?
Mon,9/11/06
Tues, 9/12/06
Wed, 9/13/06
Thurs, 9/14/06
Fri, 9/15/06
SELECT
Column1
, Column2
, etc
FROM MyTable
WHERE datepart( wk, MyDateColumn ) = 37
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>I want to write a query return particular dates of the previous week. Is
> there some function where I can pass the weeknumber (I think last week was
> 37?) and get it to return these dates, regardless of what day of the week
> I
> run the query?
> Mon,9/11/06
> Tues, 9/12/06
> Wed, 9/13/06
> Thurs, 9/14/06
> Fri, 9/15/06
>

I want to write a query return particular dates of the previous we

I want to write a query return particular dates of the previous week. Is
there some function where I can pass the weeknumber (I think last week was
37?) and get it to return these dates, regardless of what day of the week I
run the query?
Mon,9/11/06
Tues, 9/12/06
Wed, 9/13/06
Thurs, 9/14/06
Fri, 9/15/06SELECT
Column1
, Column2
, etc
FROM MyTable
WHERE datepart( wk, MyDateColumn ) = 37
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>I want to write a query return particular dates of the previous week. Is
> there some function where I can pass the weeknumber (I think last week was
> 37?) and get it to return these dates, regardless of what day of the week
> I
> run the query?
> Mon,9/11/06
> Tues, 9/12/06
> Wed, 9/13/06
> Thurs, 9/14/06
> Fri, 9/15/06
>|||Arnie,
I don't have a table, I only have the system or getdate to work with...
Perhaps I should rephrase the question:
I want to write a query return the workdays (Mon-Fri) of the previous week
base on getdate(). Ist here some function/select statement I can use where I
can pass the weeknumber (which I would get via the query (Select LAST_WEEK =(datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
Wed, Thurs, Fri, regardless of what day of the week I run the query?
"Arnie Rowland" wrote:
> SELECT
> Column1
> , Column2
> , etc
> FROM MyTable
> WHERE datepart( wk, MyDateColumn ) = 37
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
> >I want to write a query return particular dates of the previous week. Is
> > there some function where I can pass the weeknumber (I think last week was
> > 37?) and get it to return these dates, regardless of what day of the week
> > I
> > run the query?
> > Mon,9/11/06
> > Tues, 9/12/06
> > Wed, 9/13/06
> > Thurs, 9/14/06
> > Fri, 9/15/06
> >
>
>|||You may find the use of a Calendar table to be invaluable for exercises such
as this one.
Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519
Datetime -How to count the number of business days
http://www.aspfaq.com/show.asp?id=2453
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <DianeF@.discussions.microsoft.com> wrote in message
news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
> Arnie,
>
> I don't have a table, I only have the system or getdate to work with...
> Perhaps I should rephrase the question:
> I want to write a query return the workdays (Mon-Fri) of the previous week
> base on getdate(). Ist here some function/select statement I can use
> where I
> can pass the weeknumber (which I would get via the query (Select LAST_WEEK
> => (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
> Wed, Thurs, Fri, regardless of what day of the week I run the query?
>
> "Arnie Rowland" wrote:
>> SELECT
>> Column1
>> , Column2
>> , etc
>> FROM MyTable
>> WHERE datepart( wk, MyDateColumn ) = 37
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
>> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>> >I want to write a query return particular dates of the previous week.
>> >Is
>> > there some function where I can pass the weeknumber (I think last week
>> > was
>> > 37?) and get it to return these dates, regardless of what day of the
>> > week
>> > I
>> > run the query?
>> > Mon,9/11/06
>> > Tues, 9/12/06
>> > Wed, 9/13/06
>> > Thurs, 9/14/06
>> > Fri, 9/15/06
>> >
>>|||I understand, but is there a query I can write that will accomplish the same
thing?
"Arnie Rowland" wrote:
> You may find the use of a Calendar table to be invaluable for exercises such
> as this one.
> Datetime -Calendar Table
> http://www.aspfaq.com/show.asp?id=2519
> Datetime -How to count the number of business days
> http://www.aspfaq.com/show.asp?id=2453
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Diane F." <DianeF@.discussions.microsoft.com> wrote in message
> news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
> > Arnie,
> >
> >
> > I don't have a table, I only have the system or getdate to work with...
> > Perhaps I should rephrase the question:
> > I want to write a query return the workdays (Mon-Fri) of the previous week
> > base on getdate(). Ist here some function/select statement I can use
> > where I
> > can pass the weeknumber (which I would get via the query (Select LAST_WEEK
> > => > (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon, Tues,
> > Wed, Thurs, Fri, regardless of what day of the week I run the query?
> >
> >
> > "Arnie Rowland" wrote:
> >
> >> SELECT
> >> Column1
> >> , Column2
> >> , etc
> >> FROM MyTable
> >> WHERE datepart( wk, MyDateColumn ) = 37
> >>
> >> --
> >> Arnie Rowland, Ph.D.
> >> Westwood Consulting, Inc
> >>
> >> Most good judgment comes from experience.
> >> Most experience comes from bad judgment.
> >> - Anonymous
> >>
> >>
> >> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
> >> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
> >> >I want to write a query return particular dates of the previous week.
> >> >Is
> >> > there some function where I can pass the weeknumber (I think last week
> >> > was
> >> > 37?) and get it to return these dates, regardless of what day of the
> >> > week
> >> > I
> >> > run the query?
> >> > Mon,9/11/06
> >> > Tues, 9/12/06
> >> > Wed, 9/13/06
> >> > Thurs, 9/14/06
> >> > Fri, 9/15/06
> >> >
> >>
> >>
> >>
>
>|||I don't think that any such 'function' exists, and a query to accomplish
your goal would be quite Byzantine.
I suppose that you could create your own user defined function, but since
using a Calendar table makes the process so easy, I don't waste my time
trying to find kludges like that -and I recommend that you don't as well.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Diane F." <DianeF@.discussions.microsoft.com> wrote in message
news:BA5F9A11-0879-4F0A-B84E-B844D68C945C@.microsoft.com...
>I understand, but is there a query I can write that will accomplish the
>same
> thing?
> "Arnie Rowland" wrote:
>> You may find the use of a Calendar table to be invaluable for exercises
>> such
>> as this one.
>> Datetime -Calendar Table
>> http://www.aspfaq.com/show.asp?id=2519
>> Datetime -How to count the number of business days
>> http://www.aspfaq.com/show.asp?id=2453
>>
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>>
>> "Diane F." <DianeF@.discussions.microsoft.com> wrote in message
>> news:434BCED5-BDA7-469B-9E09-E5A2418DBF01@.microsoft.com...
>> > Arnie,
>> >
>> >
>> > I don't have a table, I only have the system or getdate to work with...
>> > Perhaps I should rephrase the question:
>> > I want to write a query return the workdays (Mon-Fri) of the previous
>> > week
>> > base on getdate(). Ist here some function/select statement I can use
>> > where I
>> > can pass the weeknumber (which I would get via the query (Select
>> > LAST_WEEK
>> > =>> > (datepart(wk,GETDATE())-1)) and get it to return the dates for Mon,
>> > Tues,
>> > Wed, Thurs, Fri, regardless of what day of the week I run the query?
>> >
>> >
>> > "Arnie Rowland" wrote:
>> >
>> >> SELECT
>> >> Column1
>> >> , Column2
>> >> , etc
>> >> FROM MyTable
>> >> WHERE datepart( wk, MyDateColumn ) = 37
>> >>
>> >> --
>> >> Arnie Rowland, Ph.D.
>> >> Westwood Consulting, Inc
>> >>
>> >> Most good judgment comes from experience.
>> >> Most experience comes from bad judgment.
>> >> - Anonymous
>> >>
>> >>
>> >> "Diane F." <Diane F.@.discussions.microsoft.com> wrote in message
>> >> news:E4FB872E-8DC5-4AD7-B492-BBA822270059@.microsoft.com...
>> >> >I want to write a query return particular dates of the previous week.
>> >> >Is
>> >> > there some function where I can pass the weeknumber (I think last
>> >> > week
>> >> > was
>> >> > 37?) and get it to return these dates, regardless of what day of the
>> >> > week
>> >> > I
>> >> > run the query?
>> >> > Mon,9/11/06
>> >> > Tues, 9/12/06
>> >> > Wed, 9/13/06
>> >> > Thurs, 9/14/06
>> >> > Fri, 9/15/06
>> >> >
>> >>
>> >>
>> >>
>>

I want to use in a stored procedures two databases

I have two database, database1 and database2.
Now I want to write a stored procedure in database1, in this Database I need to make a select statement to a table in database2. It is possible? What is the correct code for this problem?

Can someone help me?SELECT * FROM Database2.dbo.Table1

(assuming they are on the same server, that is).

I need help with an insert clause, please help

I am trying to write a complex insert statement and not really sure how to get started.

First I am building an application to a record label to add artist, songs, pictures, video, and music to their site.

Thanks to asp.net 2.0 building the application interface was easy, but now I realize I have run into a brick wall and need some help.

in one application, called "add artist album"

in this application, you, the website administrator, are adding information to two different tables (artist (holds all info about the artist), album (holds all the info about artist album))

I have no clue how to really start writing an insert statement for this.

Basically I want to be able to insert the album data (artistid (foreign key), album name and release date) and also have a drop down listbox, which is connected to the database by the sqldatasource connector to allow the administrator to choose an artist from the artist table so that when they add the albem and release date they, the administrator, can choose which artist in the drop down list box to associate with the album and release date data that goes in the album table. That association is made with the artistid because it is a foreign key to the artist table but how do I insert the chosen artistid number into that foreign key field for the album table?

Please can someone point me in the right direction.

I am thinking that the insert statement will be something like and insert with nested select statement and inner join included but i haven't a clue how to do this.

My table DDL

Create table artist (artistid int prmrykey, artistname varchar (100), bio varchar(100))

Create table album (albumid int prmrykey, albumname varchar (100), releasedate date, artistid int foreignkey)

My DML for the insert statement so far:

ALTER PROCEDURE sp_AddArtistAlbum
@.AlbumName varchar(50),
@.ReleasedDate datetime,
@.ArtistID int
AS

SET NOCOUNT ON

DECLARE@.AlbumIDINTINSERT INTOtb_Album

(AlbumName, ReleasedDate, ArtistID)

VALUES(@.AlbumName, @.ReleasedDate,selectArtistIDfromtb_ArtistwhereArtistID = (ArtistID from sqldatasource from the drop down listbox) )

Like I said know I am supposed to have a select subquery statement that is nested to really make this thing work but I do not know where to start, can someone please help me.

I hope I have provided enough information.

My expected results are to insert data from into the album table and have that data associated with an artist chosen in the drop down box.

Please Help!!!!!!!!!!!!!!

ALTER PROCEDUREsp_AddArtistAlbum@.AlbumNamevarchar(50),@.ReleasedDatedatetime,@.ArtistIDintASSET NOCOUNT ONDECLARE @.AlbumIDINTINSERT INTO tb_Album(AlbumName, ReleasedDate, ArtistID)VALUES (@.AlbumName, @.ReleasedDate, @.ArtistID)

The ArtistID is the SelectedValue from your DropDownList (althought the Artist Name appears in the dropdown). That's all you need to insert. It's there and available without any additional messing about.

If you look at the logic of your proposed SQL, you will see that it says "I have the ArtistID from the dropdown, Now I want to select the ArtistID from the Artist table that matches the ID I already have". Bit of a nonsense when you analyse it like that...

I need help in purchasing sql server 2005

Hi everyone, I have a web application (ASP.NET 2.0) where user will access database informations (read, write) from the application (So the SQL server is not directly exposed to the internet). These users will be authenticated from the applications itself (Not using NT/AD authentications). My question is should I get the SQL server 2005 with license(s) processor? Or should I buy the one with 5 cals ? Thanks ...

You cannot use a User CAL for a web server that access a SQL Server - you will need a device CAL instead. You therefore need to consider whether a processor license is cheaper than a license + device CAL. Your best bet is to ring up a couple of SQL Server resellers, describe your scenario and get them to quote you some prices. Then choose the best.