Getting most records possibly from an Inner/Left Join
For sample data in Excel see https://drive.google.com/file/d/17aOmG-Ynx-6U05wNNrHXJG7iywgCMiuy/view?usp=sharing (1) The fact table is from SAP. (2) Dimension Tables are named "B Masked". Please note that none of the tables had primary keys, I had to bring them in myself. In the Bracs Mapping tab of this file, there is a column GCoA which is the Account Number. Then there is BRACS which is the Bracs(source) Account number. these numbers match the same columns in the Mapping tab of the Cash Flow Pivoted file. (3) The Source Data is a combination and shorter version of the Mapping tab. Column Account match GCoA and BRACS Account match matches BRACS in the Bracs mapping tab above of the B Masked file. I noticed some similarities in the different tabs/tables so I denormalised some tabs/tables. e.g. Region Mapping and Entity Mapping in the same file I made as 1 table. Joining on not unique keys is of concern. (4) Cash Flow file, tab Sap Source is a pivoted table of the Source Data. (5) The Cash Flow tab is the final reporting structure that must be replicated in power. Column "G" in this tab is the "Function" column in the Pivoted tab. I do left joins to the Fact table and some inner join. Please advise. The Function column that makes up the bulk of giving meaning to the figures, most of the detail of this column is left out when I do a join as seen in Imeta_Bracs_Roll_Up joined to Fact file with SQL code. I brought in the Mapping table to connect the roll-up table to it as seen in Imeta_Bracs_Roll_Up joined to Bridge, this query takes. very long to run. Also, the joins take a lot of memory and some ETL/SSIS tasks may fail. What would you be bettering in this instance?
Re: Getting most records possibly from an Inner/Left Join
Στις 19/11/23 13:42, ο/η Anthony Apollis έγραψε: For sample data in Excel see https://drive.google.com/file/d/17aOmG-Ynx-6U05wNNrHXJG7iywgCMiuy/view?usp=sharing Please note that you can increase the chances of having your thoughts be read if you describe your problem using SQL or something the community can understand and refrain from giving external links for downloading files. Many people won't click on your URL or hit the download button. ... -- Achilleas Mantzios IT DEV - HEAD IT DEPT Dynacom Tankers Mgmt
Prepared statements versus stored procedures
Hi, First of all please forgive me. I'm not very experienced with databases. I was reading about prepared statements and how they allow the server to plan the query in advance so that if you execute that query multiple times it gets sped up as the database has already done the planning work. My question is this. If I make a stored procedure doesn't the database already pre-plan and optimise the query because it has access to the whole query? Or could I create a stored procedure and then turn it into a prepared statement for more speed? I was also thinking a stored procedure would help as it requires less network round trips as the query is already on the server. Sorry for the question but I'm not entirely sure how stored procedures and prepared statements work together. signature.asc Description: OpenPGP digital signature
Re: Prepared statements versus stored procedures
On Sun, Nov 19, 2023 at 10:30 AM Simon Connah wrote: > My question is this. If I make a stored procedure doesn't the database > already pre-plan and optimise the query because it has access to the whole > query? No. Planning isn't about the text of the query, it's about the current state of the database. Or could I create a stored procedure and then turn it into a prepared > statement for more speed? Not usually. I was also thinking a stored procedure would help as it requires less > network round trips as the query is already on the server. > Unless your query is insanely large this benefit seems marginal. > Sorry for the question but I'm not entirely sure how stored procedures and > prepared statements work together. They don't. David J.
Re: Prepared statements versus stored procedures
Hi Simon: On Sun, 19 Nov 2023 at 18:30, Simon Connah wrote: > I was reading about prepared statements and how they allow the server to plan > the query in advance so that if you execute that query multiple times it gets > sped up as the database has already done the planning work. But bear in mind that, if you use parameters, it does not have access to the whole query, so it has to make a generic plan. Many times it does not matter, but sometimes it does ( i.e. testing columns with very skewed value distributions, if you have an X column, indexed, where 99% of the values are 1 querying for X=1 is faster using a sequential scan when X=1 and an index scan when not, if you send X in a parameter the server does not know its real value ). > My question is this. If I make a stored procedure doesn't the database > already pre-plan and optimise the query because it has access to the whole > query? IIRC it does not, because it may not have access to all values, and more importantly, it does not have access to current statistics. Think of the typical case, preparing a database for an application, with empty tables and several procedures. On the first run, sequential scans ( to recheck for emptiness ) will be faster for every query. After some time of entering data ( and updating statistics ) better plans will surface. If you compiled the procedures on definition you would be stuck with seq scans forever. IIRC it does it once per transaction, but it should be in the docs. > Or could I create a stored procedure and then turn it into a prepared > statement for more speed? > I was also thinking a stored procedure would help as it requires less network > round trips as the query is already on the server. The main speed improvement of stored procedures is normally the less roundtrips ( and marshalling of queries back and forth ). You do not turn a stored procedure into a statement, you turn CALLING the stored procedure into a prepared statement, which may save some time but not that much, planning a call is easy. Other thing would be turning a stored procedure call into a prepared statement for an inline procedure, but this is something else. Francisco Olarte.
Re: Prepared statements versus stored procedures
On Sunday, 19 November 2023 at 18:09, Francisco Olarte wrote: > > > Hi Simon: > > On Sun, 19 Nov 2023 at 18:30, Simon Connah > simon.n.con...@protonmail.com wrote: > > > I was reading about prepared statements and how they allow the server to > > plan the query in advance so that if you execute that query multiple times > > it gets sped up as the database has already done the planning work. > > > But bear in mind that, if you use parameters, it does not have access > to the whole query, so it has to make a generic plan. Many times it > does not matter, but sometimes it does ( i.e. testing columns with > very skewed value distributions, if you have an X column, indexed, > where 99% of the values are 1 querying for X=1 is faster using a > sequential scan when X=1 and an index scan when not, if you send X in > a parameter the server does not know its real value ). > > > My question is this. If I make a stored procedure doesn't the database > > already pre-plan and optimise the query because it has access to the whole > > query? > > > IIRC it does not, because it may not have access to all values, and > more importantly, it does not have access to current statistics. Think > of the typical case, preparing a database for an application, with > empty tables and several procedures. On the first run, sequential > scans ( to recheck for emptiness ) will be faster for every query. > After some time of entering data ( and updating statistics ) better > plans will surface. If you compiled the procedures on definition you > would be stuck with seq scans forever. IIRC it does it once per > transaction, but it should be in the docs. > > > Or could I create a stored procedure and then turn it into a prepared > > statement for more speed? > > I was also thinking a stored procedure would help as it requires less > > network round trips as the query is already on the server. > > > The main speed improvement of stored procedures is normally the less > roundtrips ( and marshalling of queries back and forth ). You do not > turn a stored procedure into a statement, you turn CALLING the stored > procedure into a prepared statement, which may save some time but not > that much, planning a call is easy. > > Other thing would be turning a stored procedure call into a prepared > statement for an inline procedure, but this is something else. > > Francisco Olarte. Thank you very much for the explanation. I really appreciate it. Simon. signature.asc Description: OpenPGP digital signature
Re: Prepared statements versus stored procedures
On Sun, Nov 19, 2023 at 11:09 AM Francisco Olarte wrote: > IIRC it does it once per > transaction, but it should be in the docs. > There is no external caching for executing a CALL; the runtime executes the procedure afresh each time. If it were any different that would have to be documented. You do not > turn a stored procedure into a statement, you turn CALLING the stored > procedure into a prepared statement, Which is not possible. CALL is not a valid target for PREPARE; the valid ones are documented. The fact that store procedures do not return result sets - and are procedures - and prepared statements are not procedures and can return result sets makes any kind of direct comparison pretty meaningless in practice. They do different things and solve different problems. Know what the problem you are trying to solve is and which of the two are plausible options will make itself clear. David J.
How can I return a UTF8 string from a hex representation in a latin9 database?
Hello. Hopefully I can explain myself I have a database for a legacy application that requires an 8 bit database (i.e. the application itself won't function on a UTF8 database). Looking at ways to extend the functionality to be able to handle a few specified fields in Unicode. Had the idea to store a UTF8 string as either hex pairs or Base64 inside a VARCHAR field, which is fine. I can do that. What needs to happen though, is to build a view, that will return the decoded hex (or b64) as a UTF8 string to a client which has specified client encoding UTF8. I've tried various combinations of convert_from, and convert_to, and convert, but I just can't seem to get it to return the string a UTF8 select to the client. So if I have this data: select * from mytable; mycolumn -- ceb120ceb220ceb320ceb420ceb520cf83cf84 Then: select convert_from(decode(mycolumn, 'hex')::bytea, 'utf-8') from mytable where usr='BATCH'; ERROR: character with byte sequence 0xce 0xb1 in encoding "UTF8" has no equivalent in encoding "LATIN9" So the database encoding is still relevant , this is expected by the description of convert_from in the documentation of course. Is there some combination of functions I can use to have a client select this column from this table in a LATIN9 database and get a UTF8 string back? Any thoughts appreciated, thank you.
Re: How can I return a UTF8 string from a hex representation in a latin9 database?
On 2023-11-19 21:37 +0100, PGUser2020 wrote:
> I have a database for a legacy application that requires an 8 bit
> database (i.e. the application itself won't function on a UTF8
> database).
Technically speaking, UTF-8 is an 8-bit encoding. But I guess that
application would then show mojibake if UTF-8 were stored.
> Looking at ways to extend the functionality to be able to handle a few
> specified fields in Unicode.
>
> Had the idea to store a UTF8 string as either hex pairs or Base64
> inside a VARCHAR field, which is fine. I can do that.
Do you have to use existing Latin-9 text columns to store UTF-8? If not
then I'd go with bytea instead of text (varchar) if possible and also
supported by your client. Otherwise it may be difficult to distinguish
between "normal" Latin-9 text and the hex- or base64-encoded UTF-8.
Although bytea could also store anything, not just UTF-8, so you'd have
to deal with invalid data anyway.
> What needs to happen though, is to build a view, that will return the
> decoded hex (or b64) as a UTF8 string to a client which has specified
> client encoding UTF8.
Is the same client sending and reading that data? If yes, why can't the
client do the hex-encoding of the UTF-8 string and only send/read those
encoded strings so that database won't event see UTF-8? Why must the
database be involved in this custom encoding scheme instead of just
storing BLOBs (either as bytea or some encoded text)?
> I've tried various combinations of convert_from, and convert_to, and
> convert, but I just can't seem to get it to return the string a UTF8
> select to the client.
>
> So if I have this data:
>
> select * from mytable; mycolumn
> --
> ceb120ceb220ceb320ceb420ceb520cf83cf84
>
> Then:
>
> select convert_from(decode(mycolumn, 'hex')::bytea, 'utf-8') from mytable
> where usr='BATCH';
>
> ERROR: character with byte sequence 0xce 0xb1 in encoding "UTF8" has no
> equivalent in encoding "LATIN9"
>
> So the database encoding is still relevant , this is expected by the
> description of convert_from in the documentation of course.
>
> Is there some combination of functions I can use to have a client
> select this column from this table in a LATIN9 database and get a UTF8
> string back?
The client can disable encoding conversion by setting client_encoding to
sql_ascii:
latin9_test=# show server_encoding;
server_encoding
-
LATIN9
(1 row)
latin9_test=# set client_encoding to sql_ascii;
SET
latin9_test=# show client_encoding;
client_encoding
-
SQL_ASCII
(1 row)
latin9_test=# select
convert_from(decode('ceb120ceb220ceb320ceb420ceb520cf83cf84', 'hex'),
'sql_ascii');
convert_from
-
α β γ δ ε στ
(1 row)
Maybe that's also an option for your client.
--
Erik
pg_basebackup
Hello,
We're facing in a customer installation (PostgreSQL 13.1 on Linux) the
following problem for the first time and not reproducible:
The effective part of our backup script contains:
...
test -d ${BACKUPWAL}-${DATE}-${NUM}/ || mkdir -p ${BACKUPWAL}-${DATE}-${NUM}/
# kick to archive the current log; use a DB which will exist;
#
psql -U ${DBSUSER} -dpostgres -c "select pg_switch_wal();" > /dev/null
# backup the cluster
#
printf "%s: pg_basebackup the cluster to %s ... " "`date "+%d.%m.%Y-%H:%M:%S"`"
${BACKUPDIR}-${DATE}-${NUM}
${BINDIR}/pg_basebackup -U ${DBSUSER} -Ft -z -D ${BACKUPDIR}-${DATE}-${NUM}
...
The resulting stdout/stderr of the script:
16.11.2023-20:20:02: pg_basebackup the cluster to
/Backup/postgres/sisis-20231116-1 ...
pg_basebackup: could not receive data from WAL stream: server closed the
connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
pg_basebackup: child process exited with error 1
pg-error.log:
2023-11-16 20:34:13.538 CET [6250] LOG: terminating walsender process due to
replication timeout
Why the PostgreSQL server says something about "replication", we do
pg_basebackup?
Some more information:
- wal_sender_timeout has default value (60s)
- backup target is a local file, not a network storage
- the Linux SLES 15 server is good equipped
- nothing is logged in /var/log/messages
Any ideas? Thanks.
matthias
--
Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
Re: How can I return a UTF8 string from a hex representation in a latin9 database?
On 2023-11-20 02:59 +00:00 GMT, "Erik Wienhold" wrote:
> On 2023-11-19 21:37 +0100, PGUser2020 wrote:
>
> Technically speaking, UTF-8 is an 8-bit encoding. But I guess that
> application would then show mojibake if UTF-8 were stored.
>
Yes sorry, I should have said single byte rather than 8 bit. There must be no
possibility that a single character occupies more than one byte as the (e.g.)
varchar(10) and char(5) fields overflow lengths otherwise.
>
> Do you have to use existing Latin-9 text columns to store UTF-8? If not
> then I'd go with bytea instead of text (varchar) if possible and also
> supported by your client. Otherwise it may be difficult to distinguish
> between "normal" Latin-9 text and the hex- or base64-encoded UTF-8.
> Although bytea could also store anything, not just UTF-8, so you'd have
> to deal with invalid data anyway.
>
I do have to use existing columns yes, and they are varchar latin9 columns.
> Is the same client sending and reading that data? If yes, why can't the
> client do the hex-encoding of the UTF-8 string and only send/read those
> encoded strings so that database won't event see UTF-8? Why must the
> database be involved in this custom encoding scheme instead of just
> storing BLOBs (either as bytea or some encoded text)?
>
So one of the external clients applications which is interacting with this
database will do just that -- it will make a hex string from its utf8 input and
store that in a varchar
>
> The client can disable encoding conversion by setting client_encoding to
> sql_ascii:
>
> latin9_test=# show server_encoding;
>server_encoding
> -
>LATIN9
> (1 row)
>
> latin9_test=# set client_encoding to sql_ascii;
> SET
> latin9_test=# show client_encoding;
>client_encoding
> -
>SQL_ASCII
> (1 row)
>
> latin9_test=# select
> convert_from(decode('ceb120ceb220ceb320ceb420ceb520cf83cf84', 'hex'),
> 'sql_ascii');
> convert_from
> -
>α β γ δ ε στ
> (1 row)
>
> Maybe that's also an option for your client.
>
It is very useful and exactly what I was looking for thanks.
This technique should allow me to create a login, mask a table with a view
containing this decode, and use search_path to get the view returned in
preference to the base table.
