DearAll,
I applied the modifications by implimenting the joins,created indexes
on relevent columns which are  acting as optimizer hints-result positve
improvement in the query processing,apart from that there is one more
issue thats supposed to be considered for code reusablity,
Eg: When on more service name comes into picture then the following
code(part of procedure) is supposed to modified [Eg new service name
QTEL]
select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT MMS'
when 'ALCLICKWIN6464' then 'Airtel Click Win 646'
when 'ALMMSPORTAL' then 'Airtel MMS'
when 'ALMMSSMSDWN' then 'Airtel MMS SMS'
when 'ALMYALBUM646' then 'Airtel My Album'
when 'HINDU6397' then 'Hindu 6397'
So in such a situation can i go for a cursor instead of Select Case,I
fear cursor a lot 'cos of their severe appetite for memory- & slowing
down the dataprocessing & database server activity.
Also is it wise enough to use insert dml with a select  to load the
data of size greater than 100000 recs ? Can i use some thing like bcp
Regards
Eckhart

Eckhart (n.kopal***@gmail.com) writes:
Show quoteHide quoteIt could certainly help if you explained the actual business case a bit.
As it stands how, we only see an SQL snippet, and the risk is that
suggestions will be off-target for what you are doing.

But if you need to do the above on a larger scale, I suggest that you add a
mapping table and join with that table, rather than having to maintain a
large query.

BCP is for loading data from files (or other external sources). As I under-
stood from your previous post, you are loading data from other tables.

It is correctly understood that inserting too many rows at a time, can be
too much of a mouthful sometimes, but what is "too much" is a limit that
is constantly being pushed higher by advnaces in hardware development
and new SQL Server releases. And it is not really the number of rows
that matter - it's more the numbers in megabytes. That is, 100000 rows
with two integers is nothing. 100000 rows with there varchar(MAX) columns
with an average size of one million bytes can certainly be more than
mouthful.

The main problem is usually the transaction log, and to avoid explsion
of the log, it can sometimes be an idea to do batching and insert a
certin number of rows at a time. But this only keeps the transaction
log in check if you run with simple recovery. (Unless you back up the
log between each batch.) You must also be confident that you don't get
an inconsistent database if you batch.

For a regular table with no excessive character columns, I would say that
100000 rows is no cause for alarm. But if you need insert ten million
rows, you may want to consider batching.

The sample code you show should not be replaced with a cursor, that
would only make it worse.  It should be replaced with a table, call it
ConvertServicename with two columns, and a JOIN.  Or perhaps a LEFT
OUTER JOIN.  By using a table adding a new pair of names can be a user
transaction instead of modification to the source code of a procedure.

BCP can be a valuable tool for loading data, but it can be very picky
and annoying.  I hardly use BCP any longer since DTS became available.
The Import wizard in Enterprise Manager (2000) makes the task much
simpler in most cases.

Roy Harvey
Beacon Falls, CT

Show quoteHide quote

Hi There,
I think you can remove these sort of Scalar subqueries , by taking the
table involved to Inner Join or Left JOIN whatever suits you.
***************
(
    select musiclabel from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid
) as musiclable,
(select catid from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as catid,
(select subcatid from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as subcatid,
(select specialpackage from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as specialpackage,
(select Royalties from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as Royalties,
*****************
Select
....... CoulmnList ......,
CM.musiclabel ,
CM.catid ,
CM.subcatid ,
CM.specialpackage ,
CM.Royalties
From
datalogs.dbo.translogs TL Inner Join datalogs.dbo.cont_master CM
On CM.contentid = TL.contentid
where conditions....

and when JOIN is used it is better to have index on JOINing columns

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
http://sqloracle.tripod.com

Deep SQL .Net integration question
any performance diff using -Between- or >= and <= ?
Spreadsheet totals
Foreign Key may cause multilple cycles
Bulk Insert from Excel