Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

optimize sql statement

Hello,

How can I optimize this statement which takes time :

select *   ,
(
select name
from [server].base.sys.schemas
where name = 'TESTS' and rowid not in (select ZROWID_0 from  ods.schema.table where [ZDOS_0] = 'TEST')

) as [ZDOS_0] into #stmt_repl
from [server].base.schema.table

Thanks

bibi
0
bibi92
Asked:
bibi92
  • 3
  • 2
1 Solution
 
JoeNuvoCommented:
How many rows return from each table?

SELECT COUNT(*) FROM [server].base.sys.schemas WHERE name = 'TESTS'
SELECT COUNT(*) FROM ods.schema.table WHERE [ZDOS_0] = 'TEST'

Since I'm wonder why it will take some significant process time.
0
 
bibi92Author Commented:
7332245rows and 2345673rows
Thanks
Regards
bibi
0
 
JoeNuvoCommented:
Not sure if this will help

DECLARE @name sysname -- change datatype if need to
SELECT @name = name FROM [server].base.sys.schemas WHERE name = 'TESTS'

SELECT *, @name as [ZDOS_0]
INTO #stmt_repl
FROM [server].base.schema.table

UPDATE #stmt_repl
SET x = NULL
FROM ods.schema.table OST
WHERE #stmt_repl.rowid = OST.ZROWID_0 -- if field name is not rowid, change it to the correct one
AND OST.ro = 'TEST'

Open in new window

0
 
JoeNuvoCommented:
oops, some mistake

please change SET x = NULL

to SET [ZDOS_0] = NULL
0
 
bibi92Author Commented:
Thanks bibi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now