Will This Query Be Optimized For A Partitioned View?
Jul 20, 2005
Hello :-)
My question is: If I query a partitioned view, but don't know the values
in the "where x in(<expression>)" clause, i.e.: select * from viewA
where intVal in(select intVal from tbl1) . Compared to: select * from
viewA where intVal in(5,6).
Of course "intVal" is partitioning column.
Will this result in an optimized query that searches only the relevant
tables?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
View 1 Replies
ADVERTISEMENT
Nov 2, 2007
We have a situation where queries against a partitioned view ignore a suitable index and perform a table scan (against 200+MB of data), where the same query on the underlying table(s) results in a 4 page index seek. I can€™t find any mention of the situation, so I€™m trying a post here.
We€™re running SQL Server 2005 Enterprise edition sp2 on Windows 2003 Enterprise Edition sp1 on a two node cluster, and it also occurs on a stand-alone development box with Developer edition. We have four tables, named Options#0, Options#1, Options#2, and Options#3. All are almost identical (script generated by SSMS and edited down a bit):
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[Options#0](
[ControlID] [tinyint] NOT NULL CONSTRAINT [DF_Options#0__ControlID] DEFAULT ((0)),
[ModelCode] [char](8) NOT NULL,
[EquipmentID] [int] NOT NULL,
[AdjustmentContextID] [int] NOT NULL,
[EquipmentCode] [char](2) NOT NULL,
[EquipmentTypeCode] [char](1) NOT NULL,
[Description] [varchar](50) NOT NULL,
[DisplayOrder] [smallint] NOT NULL,
[IsStandard] [bit] NOT NULL,
[Priority] [tinyint] NOT NULL,
[Status] [bit] NOT NULL,
[Adjustment] [int] NOT NULL,
CONSTRAINT [PK_Options#0] PRIMARY KEY CLUSTERED
(
[ModelCode] ASC,
[EquipmentID] ASC,
[AdjustmentContextID] ASC,
[ControlID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
ALTER TABLE [dbo].[Options#0] WITH CHECK ADD CONSTRAINT [CK_Options#0__ControlID] CHECK (([ControlID]=(0)))
ALTER TABLE [dbo].[Options#0] CHECK CONSTRAINT [CK_Options#0__ControlID]
The only differences between the tables are in the names and in the value defaulted to and CHECKed, which matches the table name (to support the partitioned view, of course).
We receive and load data ever week and every two month, and use an unlikely algorithm to load and manage its availability by running an ATLER on the view (to maintain the access rights defined for the hosting environment). Scripted out via SSMS, the view looks like:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE VIEW [dbo].[Options] AS select * from Options#1 union all select * from Options#3
The problem is that when we issue a query like
SELECT count(*)
from Options
where ControlID = 1
and ModelCode = '2004NIC9'
The resulting query (as checked via the query plan and SET STATISTICS IO on) will get €œpartitioned€?, running against the proper table, but it will ignore the query, perform a table scan, and churn through 200+MB of data. A Similar query run against the underlying table
SELECT count(*)
from Options#1
where ControlID = 1
and ModelCode = '2004NIC9'
(with or without the ControlID = 1 clause) will perform a Clustered Index Seek and read maybe 4 pages.
Analyzing the execution plan shows that the table query work like you€™d think, but for the query against the view we get a Clustered Index Scan, with predicate:
[DBName].[dbo].[Options#1].[ControlID]=(1) AND CONVERT_IMPLICIT(char(8),[ DBName].[dbo].[Options#1].[ModelCode],0)=€™2004NIC9€™
I get the same results when explicitly listing all columns in the view. The code page on the view and tables is the same (as determined by checking properties via SSMS).
Why is the table data column being implicitly converted to the data type that it already is? Why does this occur when working with the partitioned view but not with the actual table? Can this behavior be controlled or modified without losing the (incredibly useful) data loading management benefits of the partitioned view? I€™m guessing (and hoping) it€™s some subtle quirk or mis-setting, please set me on the right path!
Philip Kelley
View 7 Replies
View Related
May 27, 2008
Schema below.
The execution plan shows that this query is correctly optimized to check only the underlying Employee_2008 table.
select * from Employee where ReportingYear = '2008'
This query is not optimized and checks both Employee_2008 and Employee_2007:
declare @ry varchar(4)
set @ry = '2008'
select * from Employee where ReportingYear = @ry
How can I get second query to be optimized correctly?
Schema:
CREATE TABLE [dbo].[Employee_2007](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Employee_2008](
[EmployeeID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
) ON [PRIMARY]
CREATE VIEW [dbo].[Employee]
AS
SELECT
'2007' ReportingYear,
EmployeeID,
Name
FROM Employee_2007
UNION ALL
SELECT
'2008' ReportingYear,
EmployeeID,
Name
FROM Employee_2008
View 3 Replies
View Related
Jun 19, 2001
I have a partitioned view containing 4 tables (example follows at end)
The query plan generated on a select correctly accesses just one of the tables
The query plan generated on an update always accesses all four of the tables. I thought that it should only access the partition required to satisfy the update. Can anyone please advise whether:
a) Is this is expected behaviour?
b) Is the partitioned view incorrectly configured in some way?
c) Is there is a known bug in this area
Note that the behaviour is the same with SP1 on SQL2000
I would be very grateful for any advice
Thanks
Stefan Bennett
Example follows
--Create the tables and insert the values
CREATE TABLE Sales_West (
Ordernum INT,
total money,
region char(5) check (region = 'West'),
primary key (Ordernum, region)
)
CREATE TABLE Sales_North (
Ordernum INT,
total money,
region char(5) check (region = 'North'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_East (
Ordernum INT,
total money,
region char(5) check (region = 'East'),
primary key (Ordernum,region)
)
CREATE TABLE Sales_South (
Ordernum INT,
total money,
region char(5) check (region = 'South'),
primary key (Ordernum,region)
)
GO
INSERT Sales_West VALUES (16544, 2465, 'West')
INSERT Sales_West VALUES (32123, 4309, 'West')
INSERT Sales_North VALUES (16544, 3229, 'North')
INSERT Sales_North VALUES (26544, 4000, 'North')
INSERT Sales_East VALUES ( 22222, 43332, 'East')
INSERT Sales_East VALUES ( 77777, 10301, 'East')
INSERT Sales_South VALUES (23456, 4320, 'South')
INSERT Sales_South VALUES (16544, 9999, 'South')
GO
--create the view that combines all sales tables
CREATE VIEW Sales_National
AS
SELECT *
FROM Sales_West
UNION ALL
SELECT *
FROM Sales_North
UNION ALL
SELECT *
FROM Sales_East
UNION ALL
SELECT *
FROM Sales_South
GO
--Look at execution plan for this query
-- This correctly only accesses the South partition
SELECT *
FROM sales_national
WHERE region = 'south'
-- Look at execution plan for update
-- This accesses all partitions - Why?
update sales_national
set total = 100
where ordernum = 23456;
View 1 Replies
View Related
Dec 17, 2007
I want to find a way to get partition info for all the tables in all the databases for a server. Showing database name, table name, schema name, partition by (maybe; year, month, day, number, alpha), column used in partition, current active partition, last partition (for date partitions I want to know if the partition goes untill 2007, so I can add 2008)
all I've come up with so far is:
Code Block
SELECT distinct o.name From sys.partitions p
inner join sys.objects o on (o.object_id = p.object_id)
where o.type_desc = 'USER_TABLE'
and p.partition_number > 1
View 3 Replies
View Related
Mar 6, 2006
Hi! This is my first post and I really need help with Partitioned View. I'm using Sql Server 2000 and I created a partitioned view using 6 tables and now a need to create the table '7' and alter the view. But when i'm trying to insert new data i'm receiving the message: :eek:
"Server: Msg 4416, Level 16, State 5, Line 1
UNION ALL view 'tb_sld_cob_pap' is not updatable because the definition
contains a disallowed construct."
My code is:
drop VIEW tb_sld_cob_pap
GO
CREATE TABLE dbo.tb_sld_cob_pap_7 (
cod_operacao int NOT NULL ,
cod_contrato int NOT NULL ,
sequencial_duplicata int NOT NULL ,
data_sld_pap smalldatetime NOT NULL CHECK ([data_sld_pap] >= '20060201'),
liqex_dia_nom_outros float NULL ,
liqex_dia_moe_outros float NULL,
constraint pk_pap7 primary key (cod_operacao,cod_contrato,sequencial_duplicata,da ta_sld_pap)
)
GO
CREATE INDEX IdxSldCobPap7_1 ON dbo.tb_sld_cob_pap_7(cod_titulo, seq_titulo, data_sld_pap)
GO
CREATE INDEX IdxSldCobPap7_2 ON dbo.tb_sld_cob_pap_7(cod_operacao, seq_ctr_sacado, sequencial_duplicata, data_sld_pap)
GO
ALTER TABLE dbo.tb_sld_cob_pap_6
DROP CONSTRAINT CK__tb_sld_co__data___6C190EBB
GO
ALTER TABLE dbo.tb_sld_cob_pap_6 ADD CONSTRAINT
CK__tb_sld_co__data___6C190EBB CHECK (((([data_sld_pap] >= '20051201') and ([data_sld_pap] < '20060201'))))
GO
create VIEW tb_sld_cob_pap
as
select * from tb_sld_cob_pap_1
union all
select * from tb_sld_cob_pap_2
union all
select * from tb_sld_cob_pap_3
union all
select * from tb_sld_cob_pap_4
union all
select * from tb_sld_cob_pap_5
union all
select * from tb_sld_cob_pap_6
union all
select * from tb_sld_cob_pap_7
My table tb_sld_cob_pap_6 does NOT have data with ([data_sld_pap] >= '20060201').
I'm using this script in other database and I don't have this problem.
Thank you...
View 6 Replies
View Related
Jun 18, 2007
USE Northwind
GO
CREATE TABLE myTable99_1 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/1999' and PostDate < '12/31/1999 23:59:59'))
CREATE TABLE myTable99_2 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/2000' and PostDate < '12/31/2000 23:59:59'))
CREATE TABLE myTable99_3 (
Accountchar(3)
, Ledgerchar(4)
, PostDatedatetime
, PRIMARY KEY (Account, Ledger)
, CHECK(PostDate> '1/1/2001' and PostDate < '12/31/2001 23:59:59'))
CREATE INDEX myTable99_1_IX ON MyTable99_1
(Account, Ledger)
CREATE INDEX myTable99_2_IX ON MyTable99_2
(Account, Ledger)
CREATE INDEX myTable99_3_IX ON MyTable99_3
(Account, Ledger)
GO
CREATE VIEW myView99
AS
SELECT Account
, Ledger
, PostDate
FROM myTable99_1
UNION ALL
SELECT Account
, Ledger
, PostDate
FROM myTable99_2
UNION ALL
SELECT Account
, Ledger
, PostDate
FROM myTable99_3
GO
SELECT * FROM myView99 WHERE Account = 1 AND Ledger = 1
GO
DROP VIEW myView99
DROP TABLE myTable99_1, myTable99_2, myTable99_3
GO
OK, so I thought I knew this, but I'm looking for parallelism...not only am I no getting it, I'm getting an Index scan....is it becuse I didn't put any data in the table? I thought it would stil show my index seek with parallelism
What up, homey?
View 5 Replies
View Related
Mar 7, 2012
This post concerns updating across a partitioned view, and not unlike others about this subject I am getting this error:
Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'dbII.dbo.MyTable' is not updatable because a partitioning column was not found.
I am aware of the rules for defining a partitioning column, but interpreting them may have beaten me. So perhaps I haven't abided by all the rules. How to spot which one(s) from the view and table definitions? I suspect the CHECK constraint does not allow the ASCII function, but I can't see how to avoid using it given SYSCODE entries in one table are like "[A-Z]%" and in the other are like "[0-9]%".
Otherwise, I suspect it is because one of the tables has, by legacy, a text column and the view is casting it to varchar(MAX). I also suspect it is because there's a second column with a unique index. These aren't mentioned in the rules (are they?).
Here's the view definition:
SELECT SYSCODE, COL2, CAST(COMMENTS AS varchar(MAX)) AS COMMENTS
FROM dbo.MYTABLE
UNION ALL
SELECT SYSCODE, COL2, COMMENTS
FROM OTHERDATABASE.dbo.MYTABLE AS MYTABLE_1
And here are the table definitions:
-- Table in the database where view is defined
CREATE TABLE [dbo].[MYTABLE](
[SYSCODE] [char](12) NOT NULL,
[Code]....
View 1 Replies
View Related
Oct 18, 2007
Hi all,
I am designing 3 p:artitioned views for 3 tables. Those tables grow up in 1.5 millions of rows per month (each one), so I decided to partition those tables monthly. The issue is that if I want to create the views with more than 256 months (256 tables) SQL Server says: 'Server: Msg 106, Level 15, State 1, Procedure Jugadas, Line 258Too many table names in the query. The maximum allowable is 256.'
Is there any workaround for this?
Another solution maybe?
PD1: I've tested with less than 256 tables and it works fine, I can update and query the tables (except for a couple of querys where I've got to join 2 or more of the involucred views in which case I got a similar error saying about a 260 table limit).
View 2 Replies
View Related
Nov 13, 2007
I have a table that I'm trying to scale out into a partitioned view. It's about 30 million rows. It's a workflow table and I have a taskID in the table. Originally the table was partitioned on this column but performance still wasn't what I wanted it to be, so we figured out how we could partition on a bit flag of IsOpen.
Question #1) Anyone know a best practice for creating apartitioned views on multi-columns?
What I'd like to try to do to lower the complexity of the original partitioned view is to create a view of partitioned views. Is this even possible (This is Q#2, BTW).
View 1 Replies
View Related
Jan 13, 2004
I have a partitioned view defined by a UNTION ALL of member tables. I can update the member tables through the view without any problem. However, when I declare a cursor on this partitioned view and try to update the view using WHERE CURRENT OF, I get an error saying 'The target object type is not updatable through a cursor'. Does anyone know if it's the case that updating a partitioned view through cursor is not supported in SQL Server 2000?
Thanks
View 2 Replies
View Related
Sep 8, 2014
I have database with a large table (30 Billion rows) because it is so big I separated the data in quarterly tables and created a partitioned view (with hints for the date column) about 1 billions a quarter. (all in separated filegroups). The tables themselfes are partitioned by date again, so you slice out one day
However the full-backup of grows and grows and the mainpart of it is "old" but needed data.
So I was thinking to put the older data in a separate database (with separated backup) and then point to the table in my view.
While this is technical possible (leaving out the WITH SCHEMABINDING) I wonder what negative consequences it will have.
I already had to lose "with schemabing".
I have to use separate partioning functions - for each database its own - (partition schemas where already separated due to separated filegroups)
What about query optimization, does the optimizer care that there are two databases?
View 6 Replies
View Related
Jan 23, 2008
Using SQL Server 2005. Defined partitioned view with computed column. Computed column was a constant varchar. Ran a SELECT. According to Query Execution Plan, SQL did recognize the computed column as the partitioning column and used it to optimize the query.
However MSDN says a computed column cannot be used as the partitioning column.
Could someone from MS clarify?
View 2 Replies
View Related
May 14, 2006
Hello,
please enlighten me regarding an issue with partitioned view... There are 3 tables in my DB of a similar structure:
CREATE TABLE Table1 (value1 varchar(1))
CREATE TABLE Table2 (value1 varchar(1))
CREATE TABLE Table3 (value1 varchar(1))
INSERT INTO Table1 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
INSERT INTO Table2 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
INSERT INTO Table3 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
As sometimes we need to access all data from these tables, a view has been created:
CREATE VIEW AllData AS
SELECT value1, '1' as table_id from Table1
UNION ALL
SELECT value1, '2' as table_id from Table2
UNION ALL
SELECT value1, '3' as table_id from Table3
The problem is that while running a query like
SELECT * from AllData WHERE value1 = 'a' and table_id = '3'
I see a table scan being performed on all 3 tables, not just table3 - i.e optimisation engine doesn't care for my table_id computed column and for that fact that required data is located ONLY in Table3.
Is there any way to force optimiser to consider this column andrrebuild a plan? If not - how can I rebuild a view (I can't modify tables) to achieve that? Maybe create an index for a view?
Thanks in advance. RTFM and search don't seem to clarify this for me...
View 8 Replies
View Related
Oct 5, 2007
I am setting up 3 Linked Servers (SERVER_A, SERVER_B and SERVER_C) in an isolated local network. They are all running SQL Server 2005 Developer Edition, all on XP SP2. On each server, I have a distributed partitioned view named WAREHOUSE_ALL that basically is the UNION of all WAREHOUSE tables.
I am having trouble in running write (INSERT, UPDATE or DELETE) queries on the distributed partitioned view. The error returned was (run from SERVER_B)
OLE DB provider "SQLNCLI" for linked server "SERVER_A" returned message "No transaction is active.". Msg 7391, Level 16, State 2, Line 7The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SERVER_A" was unable to begin a distributed transaction.
However, executing a read (SELECT) query ran smoothly without error.
I have done all the steps required as described in the article at http://support.microsoft.com/?kbid=873160 . Note that the only difference between the situation and our situation is the provider (SQLOLEDB and SQLNCLI), which I guess does not important. Unfortunately, the error still comes out.
After reading heaps of other article, I suspected that there is something wrong with MSDTC. As far as I know, all the settings for MSDTC were set accordingly. Then, I ran DTCPing - http://www.microsoft.com/downloads/details.aspx?FamilyID=5e325025-4dcd-4658-a549-1d549ac17644&DisplayLang=en and the error returned was
DTCping log file: C:Documents and SettingsAdministratorDesktoplSERVER_B2496RPC server is ready
Please Start Partner DTCping before pinging
++++++++++++Validating Remote Computer Name++++++++++++
Please refer to following log file for details:
C:Documents and SettingsAdministratorDesktoplSERVER_B2496.log
Invoking RPC method on SERVER_C
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed
And here is the log file:
Platform:Windows XP
IP Configure Information
Host Name . . . . . . . . . : SERVER_B
DNS Servers . . . . . . . . : 129.78.99.2
Node Type . . . . . . . . . :
NetBIOS Scope ID. . . . . . :
IP Routing Enabled. . . . . : no
WINS Proxy Enabled. . . . . : no
NetBIOS Resolution Uses DNS : no
Ethernet adapter {4404F3CB-F4B7-4990-912C-E69721C885B1}:
Description . . . . . . . . : 3Com EtherLink XL 10/100 PCI TX NIC (3C905B-TX) #2 - Packet Scheduler Miniport
Physical Address. . . . . . : 00-01-02-85-B8-A9
DHCP Enabled. . . . . . . . : no
IP Address. . . . . . . . . : 172.19.102.35
Subnet Mask . . . . . . . . : 255.255.255.0
Default Gateway . . . . . . : 172.19.102.250
DHCP Server . . . . . . . . : 255.255.255.255
Primary WINS Server . . . . : 0.0.0.0
Secondary WINS Server . . . : 0.0.0.0
Lease Obtained. . . . . . . : Thu Jan 01 00:00:00 1970
Lease Expires . . . . . . . : Thu Jan 01 00:00:00 1970
++++++++++++lmhosts.sam++++++++++++
++++++++++++hosts ++++++++++++
127.0.0.1 localhost
++++++++++++++++++++++++++++++++++++++++++++++
DTCping 1.9 Report for SERVER_B
++++++++++++++++++++++++++++++++++++++++++++++
RPC server is ready
++++++++++++Validating Remote Computer Name++++++++++++
10-05, 17:10:54.769-->Start DTC connection test
Name Resolution:
SERVER_C-->172.19.102.36-->SERVER_C
10-05, 17:11:09.781-->Start RPC test (SERVER_B-->SERVER_C)
Problem:fail to invoke remote RPC method
Error(0x5) at dtcping.cpp @303
-->RPC pinging exception
-->5(Access is denied.)
RPC test failed
I guess it could be due to port problem, which I have already opened in the Windows Firewall. There is one article which is confusing me -> Update to automatically open port 135 in Windows Firewall when a TCP or a UDP RPC server registers with the endpoint mapper at http://support.microsoft.com/kb/838191 (This article shows automatic opening of port 135!)
Please help me. Thanks.
View 8 Replies
View Related
May 24, 2006
Greetings once again my SQL friends,
I am getting the following error when I attempt to complete my data flow task. The destination is a partitioned view but I get the following error message when I run the package :
Partitioned view 'PRICE_DIM' is not updatable as the target of a bulk operation
How to solve this problem?
View 1 Replies
View Related
Dec 7, 2000
I have a problem while I try to insert data into a partioned view I am
getting the following error.
Server: Msg 4436, Level 16, State 12, Line 9
UNION ALL view 'sales_all' is not updatable because a partitioning column
was not found.
Any thoughts
USE pubs
CREATE TABLE sales_monthly
( sales_month int NOT NULL ,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_jan
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
CREATE TABLE sales_feb
( sales_month int NOT NULL,
sales_qty int NOT NULL
)
GO
ALTER TABLE sales_feb WITH NOCHECK ADD
CONSTRAINT PK_sales_feb PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_feb CHECK (sales_month = 2)
GO
ALTER TABLE sales_jan WITH NOCHECK ADD
CONSTRAINT PK_sales_jan PRIMARY KEY CLUSTERED
( sales_month
) ,
CONSTRAINT CK_sales_jan CHECK (sales_month = 1)
GO
View 2 Replies
View Related
Jul 25, 2005
Hi,
While creating an indexed view with the command :
create unique clustered index idx_atrid on account_transactions (policy)
there is a check constraint on the policy column of the tables used in this view.
The following error is encountered
Cannot index the view 'test.dbo.account_transactions'. It contains one or more disallowed constructs.
Can anyone help?
View 1 Replies
View Related
Jul 23, 2005
We have a partitioned view with 4 underlying tables. The view and eachof the underlying tables are in seperate databases on the same server.Inserts and deletes on the view work fine. We then add insert anddelete triggers to each of the underlying tables. The triggers modifya different set of tables in the same database as the view (differentthan the underlying table). The problem is those triggers aren't firedwhen inserting or deleteing via the view. Inserting or deleteing theunderlying table directly causes the the triggers to fire, but not whenthe tables are accessed as a result of using the view.Am I missing something? The triggers are 'for insert' and 'fordelete'. No 'instead of' or 'after' triggers.
View 4 Replies
View Related
Jul 20, 2005
I am using SQL Server 2000, SP3.I created an updatable partitioned view awhile ago and it has beenrunning smoothly for some time. The partition is on a DATETIME columnand it is partitioned by month. Each month a stored procedure isscheduled that creates the new month's table, and alters the view toinclude it. Again... working like a charm for quite some time.This past weekend I moved some of the first tables onto a new filegroup. I did this through Enterprise Manager, by going into designmode for the table, then going into the properties for the table andchanging the file group there as well as in all of the indexes. Nowthe partitioned view is no longer updatable. It gives the errormessage: "UNION ALL view '<view name>' is not updatable because apartitioning column was not found."I have extracted the DDL for all of the partition tables and comparedthem and they all look the same. I checked and then double-checked theCHECK constraints to make sure that they were all valid and they are.If I remove the tables that I moved to the new file group from theview, then it is once again updatable, but when I put them back in itfails again.Any ideas? If you would like samples of the code then I can send italong, but it's rather large, so I have not included it here.Thanks!Thomas R. Hummel
View 3 Replies
View Related
Jan 14, 2007
let said, i have a table which contains a column with value such as:
,1,2,3,4,5,6,
,3,4,5,
,1,2,4,
and then i use the query:
select col1, col2 from table (nolock) where (col3 like ',1,' or col3 like ',3,')
is there any function which can be used to speed up the query or which more optimized one? Maybe something similar to IN(xxxx,xxxx,xxxx)
View 8 Replies
View Related
Jun 13, 2008
I started with this query:
SELECT procs.name as ProcName,
params.name as ParameterName,
types.name as ParamType,
params.max_length,
params.precision,
params.scale,
params.is_output,
params.has_default_value
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
AND params.user_type_id = types.user_type_id
WHERE procs.is_ms_shipped = 0
AND params.name = '@DISPOSAL_AREA_NAME'
AND procs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'
ORDER BY procname,
params.parameter_id
Now, all I need from it is the column params.is_output.
I have modified it down to what I need, but I'm wondering if I can remove some of the joins or anything else for better performance without losing the proper results:
SELECT params.is_output
FROM sys.procedures procs
LEFT OUTER JOIN sys.all_parameters params
ON procs.object_id = params.object_id
LEFT OUTER JOIN sys.types types
ON params.system_type_id = types.system_type_id
AND params.user_type_id = types.user_type_id
WHERE procs.is_ms_shipped = 0
AND params.name = '@DISPOSAL_AREA_NAME'
AND procs.name = 'webservices_BENEFICIAL_USES_DM_SELECT'
View 2 Replies
View Related
Nov 28, 2007
Hello All,
I have this query that is taking more than 5 minutes to run, granted it involves 7 tables, 4 of which have over 100000+ rows, but there must be a quicker way of executing this.
Code Block
SELECT
ACP.COMPANY_NAME,
WOD.WO ,
WOH.SCHEDULED_DATE ,
WOH.JOB_ADDRESS_1,
WOH.JOB_ADDRESS_2,
WOH.CUSTOMER_CODE,
ARC.CUSTOMER_NAME,
ARC.BILL_TO_CUSTOMER_CODE,
APS.SUPPLIER_NAME,
APC.INVOICE_NUMBER as AP_INVOICE_NUMBER,
APC.INVOICE_DATE as AP_INVOICE_DATE,
APC.DATE_OF_RECORD as AP_DATE_OF_RECORD,
WOD.AMOUNT,
APC.CHEQUE_NUMBER,
WOH.INVOICE_NUMBER as AR_INVOICE_NUMBER,
ARI.DATE_OF_RECORD as AR_DATE_OF_RECORD
FROM
WO_WODDescription_tbl AS WOD
LEFT OUTER JOIN WO_Headers_tbl AS WOH ON WOD.COMPANY_CODE = WOH.COMPANY_CODE AND WOD.WO = WOH.WORK_ORDER_NUMBER
LEFT OUTER JOIN AP_CurrentDetails_tbl as APC ON WOD.COMPANY_CODE = APC.COMPANY_CODE AND WOD.DRILL_DOWN_NUMBER = APC.DRILL_DOWN AND WOD.AUDIT_NUMBER = APC.AUDIT_NUMBER
LEFT OUTER JOIN AR_CustomerMaster_tbl as ARC ON WOD.COMPANY_CODE = ARC.COMPANY_CODE AND WOH.CUSTOMER_CODE = ARC.CUSTOMER_CODE
LEFT OUTER JOIN AP_Suppliers_tbl as APS ON APC.COMPANY_CODE = APS.COMPANY AND APC.SUPPLIER_CODE = APS.SUPPLIER_CODE
LEFT OUTER JOIN ADM_CompanyProfile_tbl as ACP ON WOD.COMPANY_CODE = ACP.COMPANY_CODE
LEFT OUTER JOIN AR_InvoiceDetailCurrent_tbl as ARI ON WOD.COMPANY_CODE = ARI.COMPANY_CODE AND WOH.INVOICE_NUMBER = ARI.INVOICE_NUMBER
WHERE
(WOD.COMPANY_CODE = '01' OR WOD.COMPANY_CODE = '03')
AND APC.CHEQUE_NUMBER <> 'X%'
AND (APC.DATE_OF_RECORD < '20061101' AND ARI.DATE_OF_RECORD > '20061031')
ORDER BY WOD.COMPANY_CODE, WOD.WO
Can anyone give me any suggestions of how I could speed this up?
Also, I have noticed that sqlservr.exe is using more than 1.5GB of the 2GB in the machine while doing conversions from flat files to the database while the CPU is under 3% load, is this action typical of MSSQL2005?
Any help with this would be greatly appreciated.
View 3 Replies
View Related
Oct 9, 2015
I am trying to use an indexed view to allow for aggregations to be generated more quickly in my test data warehouse. The Fact Table I am creating the indexed view on is a partitioned clustered columnstore index.
I have created a view with the following code:
ALTER view dbo.FactView
with schemabinding
as
select local_date_key, meter_key, unit_key, read_type_key, sum(isnull(read_value,0)) as [s_read_value], sum(isnull(cost,0)) as [s_cost]
, sum(isnull(easy_target_value,0)) as [s_easy_target_value], sum(isnull(hard_target_value,0)) as [s_hard_target_value]
, sum(isnull(read_value,0)) as [a_read_value], sum(isnull(temperature,0)) as [a_temp], sum(isnull(co2,0)) as [s_co2]
, sum(isnull(easy_target_co2,0)) as [s_easy_target_co2]
, sum(isnull(hard_target_co2,0)) as [s_hard_target_co2], sum(isnull(temp1,0)) as [a_temp1], sum(isnull(temp2,0)) as [a_temp2]
, sum(isnull(volume,0)) as [s_volume], count_big(*) as [freq]
from dbo.FactConsumptionPart
group by local_date_key, read_type_key, meter_key, unit_key
I then created an index on the view as follows:
create unique clustered index IDX_FV on factview (local_date_key, read_type_key, meter_key, unit_key)
I then followed this up by running some large calculations that required use of the aggregation functionality on the main fact table, grouping by the clustered index columns and only returning averages and sums that are available in the view, but it still uses the underlying table to perform the aggregations, rather than the view I have created. Running an equivalent query on the view, then it takes 75% less time to query the indexed view directly, to using the fact table. I think the expected behaviour was that in SQL Server Enterprise or Developer edition (I am using developer edition), then the fact table should have used the indexed view. what I might be missing, for the query not to be using the indexed view?
View 1 Replies
View Related
Oct 17, 2006
This one has me stumped.
I created an updateable partioned view of a very large table. Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.
There error generated is:
Server: Msg 16957, Level 16, State 4, Line 3
FOR UPDATE cannot be specified on a READ ONLY cursor
Here is the cursor declaration:
declare some_cursor CURSOR
for
select *
from part_view
FOR UPDATE
Any ideas, guys? Thanks in advance for knocking your head against this one.
PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing. Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.
View 2 Replies
View Related
Mar 29, 2015
I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE)
- Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name.
2. I've created a CHECK CONSTRAINT on the server name column on each server.
3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2.
4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].[table]
UNION ALL SELECT * FROM [server2].[database].[dbo].[table]
UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:
SELECT *
FROM [database].[dbo].[partioned_view_name]
WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.
View 9 Replies
View Related
Mar 9, 2006
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies
View Related
Jul 21, 2005
Is it some how The following sp can be optimized?
IF @groupID='812846'
BEGIN
IF (SELECT count(*) from Employee where SSN= @SSN and groupID=@groupID) > 0
BEGIN
UPDATE Employee
SET NameLast=@LastName,
NameFirst=@FirstName,
NameMiddle=@MI,
WHERE SSN= @SSN and GroupId=@GroupId
select @EmpId=EmpId from Employee where SSN= @SSN and groupID=@groupID
END
ElSE
BEGIN
insert into Employee (GroupId, NameLast, NameFirst, NameMiddle,SSN)
values (@GroupId, @LastName, @FirstName, @MI, @SSN)
select @EmpId = @@IDENTITY
END
END
else
BEGIN
insert into Employee (GroupId, NameLast, NameFirst, NameMiddle, SSN)
values
(@GroupId, @LastName, @FirstName, @MI, @SSN)
select @EmpId = @@IDENTITY
END
View 8 Replies
View Related
Feb 21, 2007
is there a better code for this..?
SELECT phase, stat, subject, CASE WHEN phase = 'Initial/Data Collection' THEN '1'
WHEN phase = 'Screening' THEN '2'
WHEN phase = 'Assessment and Selection' THEN '3'
WHEN phase = 'Placement' THEN 4 END AS PhaseSort
FROM (SELECT subject, stat, CASE WHEN stat = 'Application Received' THEN 'Initial/Data Collection'
WHEN stat = 'Shortlisted' OR
stat = 'For Screening' THEN 'Screening'
WHEN stat = 'For Assessment' OR
stat = 'Passed Initial Evaluation' OR
stat = 'Passed Profiles Exam' OR
stat = 'Passed Technical Exam' THEN 'Assessment and Selection'
WHEN stat = 'For Placement' THEN 'Placement' END AS phase
FROM (SELECT subject, CASE WHEN subject = 'Process Application' OR
subject = 'Application Received' THEN 'Application Received'
WHEN subject = 'Screen Application' THEN 'For Screening'
WHEN subject = 'Phone interview' THEN 'Shortlisted'
WHEN subject = 'Initial Interview' THEN 'For Assessment'
WHEN subject = 'Profiles assessment'THEN 'Passed Initial Evaluation'
WHEN subject = 'Technical Exam and Interview' THEN 'Passed Profiles exam'
WHEN subject = 'background and reference check' THEN 'Passed Technical Exam'
WHEN subject = 'Job Offer' OR
subject = 'Contract Signing' THEN 'For Placement' END AS stat
FROM dbo.filteredtask
WHERE (subject = 'application received') OR
(subject = 'process application') OR
(subject = 'screen application') OR
(subject = 'initial interview') OR
(subject = 'profiles assessment') OR
(subject = 'technical exam and interview') OR
subject = 'background and reference check' OR
subject = 'phone interview' OR
subject = 'shortlisted' OR
subject = 'For Placement' OR
subject = 'job offer' OR
subject = 'contract signing') Phases) stats
ORDER BY phasesort
__________________________________________________
Your future is made by the things you are presently doing.
Andrew
View 6 Replies
View Related
Feb 13, 2008
Any idea how I could do this efficiently?
For example, the SiteName & SLAClass field using select statements each time may bog down the system.
Also, I’d like to feed the CustID and Subject fields from another table call Profile instead of typing the CustID field each time.
The result of this statement is to search for customers in the subject line and if customer is found then add the customer information into the Detail table. The Profile table contains all customer information.
UPDATE [TEST3].[dbo].[Detail]
SET [CustID] = 'Book Fairs' /*fill in with field from the Profile table automatically*/
,[SiteName] = (SELECT distinct([Profile].[SiteName] )
FROM [TEST3].[dbo].[Profile], [TEST3].[dbo].[Detail]
WHERE [Profile].[CustID] = [Detail].[CustID])
,[SLAClass] = (SELECT distinct([Profile].[SLAClass])
FROM [TEST3].[dbo].[Profile], [TEST3].[dbo].[Detail]
WHERE [Profile].[CustID] = [Detail].[CustID])
WHERE [Detail].[CallID] IN
(SELECT [CallLog].[CallID] FROM [TEST3].[dbo].[CallLog], [TEST3].[dbo].[Subset], [TEST3].[dbo].[Asgnmnt]
WHERE [CallLog].[CallType] = 'DREAM' AND
[CallLog].[Subject] LIKE '%Book Fairs%' ) /*fill in with field from the Profile table automatically*/
View 6 Replies
View Related
Jul 20, 2005
I have two tables in SQL 6.5 database with identical fields and indexes. Onecontains the data of August 2003 and other July 2003. Now the august tableis larger ( about 40000 more rows ) than the july table but i've noticedthat the same queries perform much faster on the august table than the julytable. Ive tried this with many different queries so i'm wondering whats thereason behind this. Is there a way to optimize a table? Remember , I'm usingSQL 6.5thx
View 4 Replies
View Related
Sep 30, 2006
Hi Experts,
I have following doubts on join condition
Table 1 primary key (id,sub)
Name id sub marks
xxx 61 maths 45
xxx 61 science 50
another table primary key ( id,language)
id language write
61 english yes
61 Hindi no
Output:
Xxx 61 maths 45 english yes
Xxx 61 maths 45 Hindi no
Xxx 61 science 50 english yes
Xxx 61 science 50 hindi no
how to join these tables to get
every information in 2 rows will it possible
xxx 61 maths 45 English yes
xxx 61 science 50 hindi no
please suggest me to right path
thanking u
please mail to me:nallisalmon@yahoo.co.in
View 1 Replies
View Related
Feb 1, 2005
Hi ,
I created a page that list the total of hours, lunch time and expenses for the employees of the company.
I am trying to optimize this stored procedure , but it still takes more than 40 seconds for 50 employees.
select @StartDate As DateLigne, TPerson.Name, TPerson.idperson,
(select sum(coalesce(hours,0) - coalesce(lunch,0)) FROM Thereport
WHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As hours,
(select sum(coalesce(nonbillable,0)) FROM Thereport
WHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As nonbillable,
(select sum((coalesce(miles,0)*@mil)+ coalesce(perdiem,0)+coalesce(supplies,0)+coalesce(airfare,0)+ coalesce( gas,0) + coalesce(autorental,0)+ coalesce(other,0) ) FROM ThereportWHERE etridperson=TPerson.idperson AND etridproject=TUserProject.etridproject AND DateDIFF(day, @StartDate, datereport) >= 0 AND DateDIFF(day, datereport, @endDate) >= 0 ) As Expenses
FROM TUserProject, TPerson
WHERE TUserProject.etridperson=TPerson.idperson AND etridproject =89
Do you have any idea of how I could optimize this stored procedure?
Thanks
View 8 Replies
View Related
