Ester Cohen poem

i have few memories of being young. I remember walking in the fields with my father, and having him tell me to look at the birds flying and looking around wildly, trying to find them. My father was greatly relieved when he learned i needed glasses – he thought i was just a little ‘slow’. I remember a picture and maybe the event of going to school carrying a handful of flowers for my teacher. I was told that i always looked like ‘a little old man’ even as a child. I remember in kindergarten protesting that my brand-new sandals were NOT girl shoes. I convinced the first person that was true, the second never even hestitated: “Dick has GIRL shoes on.” I remember not being allowed to visit one of my father’s acquaintances who had a room full of electronics because “Dick would make one of his mad bull rushes” and knock everything down. I remember pouring milk over my lap in cub-scout camp at the table with other cubs and parents, and saying i would go back to the camp ground and change. My father had to come to get me since i was not intending to come back to breakfast.

Maybe i have LOTS of memories of being young, just not happy ones.

Did i have a typical youth? Does everyone have similar experiences if not similar memories? Was this little boy unlucky, or was he persecuted by a Loving God? Job at age 7.

Really, now – assume that everything happens for a reason. Every moment is a potential teaching moment. I certainly learned things, but i am not confident i learned what i was supposed to have learned. I might also wonder about my human teachers, not only the ones in school.

All those unpleasant memories are gifts from God, or rather the opportunties that engendered them were gifts. “Richard, my son, here is a holy moment you can learn humility, or self-respect, or courage, or …” I seem to be a bad student. I seemed to have learned that it is going to hurt and that it will never go away and that hurt is fatal. So i must hide so that death does not find me. Or maybe that if sufficiently pickled, i will last forever. Not that i want that, either.

It was second or third grade and the teacher sent me to the office with a message to give to someone. I was very proud at this and wished everyone could see me. There were a couple of teachers in the hall who were talking and they DID see me. I walked right through the group, proudly, said excuse me, and continued on — till i was called back. Now, i learned something from that – i would NEVER walk through a bunch of townies on Dixwell or a bumch of drug dealers hanging out. However, i will also do almost anything to avoid attention. I wished that i had had someone who could have helped me process it then – now these and other memories are so painful that i almost can’t bear to remember it.

Maybe i am processing it now – ineffectively. Sometimes i wish i could punish myself sufficiently to receive absolution for my having been a child and doing childish things. I keep trying, I also keep doing childish things. And i keep beating myself. How does that work for me?

I read a poem by Ester Cohen referenced on onBeing.org – part of her series on “Postcards from Passover“. The title of the poem is, i guess, (but it IS the title of the post): “Forgiveness, Say That It Is Possible.” I wrote someplace else that i have never, never forgiven a wrong — that was DONE BY ME. This poem hopes ‘to forgive whatever happened to us: betrayals, lies, even annihilation” I have this terrible, desperate horrible fear that whatever happened TO that child — without the experience or understand or capacity to learn from life and in the absence of lovingkindness around him, needs forgiveness. WHO should i forgive? WHAT am i to learn?  WHO should i forgive first?

For Passover this year
I’d like to invite
people I love, some
I like well enough
some who are in the OK category
to picture a word where we all
in the course of the meal
say that it is possible
to forgive whatever happened to us:
betrayals, lies, even
annihilation. Some of life
so terrible it’s hard to believe
what people can do to one another
difference makes us crazy
how could he why did she
different values religions ideas
close family friend for 30 years
he didn’t invite us to his wedding
my husband’s Armenian relatives
all killed by Turks. Hurt is not
an abstraction an idea. There is
always a reason why, an
unsatisfactory explanation.
Weakness insanity priorities even
personality. This Passover
maybe we can
liberate ourselves
from holding onto
what happened before
we don’t have to subscribe
to Be Here Now but we can try
letting go of all that gets in the way
of how Pink Egypt is
how much we can love one another
this Passover we will pass over
injustice because we want to try
a holiday experiment
maybe just this year, we can liberate ourselves
by saying these words out loud
Forgive
Forgive
Forgive
— ESTHER COHEN

Posted in Uncategorized

XML Shredding to SQL

The article in the simple-talk newsletter continues on talking about XML shredding, specifically using XQUERY rather than OPENXML.  The result of the shredding is that the XML is deconstructed into a typical database table.

Basically, you do a traditional select statement from a table that includes columns of XML data, but you specify the columnname.value(specification) to pull the specified XML data (value) from the XML column.  The specification looks like

column-name.value(‘declare namespace ns=”http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey”; (/ns:IndividualSurvey/ns:elementname)[1]’,’varchar(50)’) AS ElementName,…

Notice that we name the column we  want, append the .value operator, declare the namespec, give the query the XPATH to what we want, and finally the SQLType of the data that we want to cast\convert to.  And the name of the column as an alias.

Note that the value operator demands that we have a singleton as a return – not sure about null, but we can’t return more than one item.  Which works in this case since this is row by row.

Posted in Uncategorized

mySQL dos batch file to import

We have to hack into a mySQL database. After i have created a number of powershell\sql scripts from the SQLDUMPfile, some to create tables and others to import data, we have to LOAD those script, which are in fact sql scripts. The dump file is 52GB, which would strangle the workbench, which is why the separation of tables and import.

The imports can fit into a single file and we can run it from the workbench, but SOME of the imports have too much data in them. There is also overhead for the workbench and one time the script was still too big and i lost the session.

So run it from the command line. I want to make it into a batch\DOS file so i can just execute it. When that works, i can cluster together a number of import scripts to get the bunches of smaller files together. Like all the files that have no data, and turn out to be 1KB in size?

I can start mySQL from the command line, but it took awhile to get the script to auto-login with the password. And THEN to recognize the right database. Turns out that that is best done by calling the import script when the SCRIPT contains the “use database;” syntax, and not in the batch. I could manually load the script from the mySQL prompt, as mySQL> -e “source importfile.sql”, but that’s no fun. I am sure there are other ways too.

What i found was this .bat file

“C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe” -u rootie -p##### < d:\scripts\importscripts\forms2.sql

will run, start mySQL, use the password (notice no space) albeit with a warning, and load/run the script forms2.sql. That file looks like this:

use database;
LOCK TABLES `forms` WRITE;
/*!40000 ALTER TABLE `forms` DISABLE KEYS */;
/*!40000 ALTER TABLE `forms` ENABLE KEYS */;
UNLOCK TABLES;
LOCK TABLES `mastermachines` WRITE;
/*!40000 ALTER TABLE `mastermachines` DISABLE KEYS */;
/*!40000 ALTER TABLE `mastermachines` ENABLE KEYS */;
UNLOCK TABLES;
LOCK TABLES `mytable` WRITE;
/*!40000 ALTER TABLE `luprolinkraw` DISABLE KEYS */;
INSERT INTO `luprolinkraw` VALUES ('3ac160e5-1a41-4213-98e6-555a574238ac','…’,’2014-06-16 18:04:11′,1);
/*!40000 ALTER TABLE `mytable` ENABLE KEYS */;
UNLOCK TABLES;

There are three sql statements in this script – set keys on forms and mastermachines. These are actually empty tables, but the SQLDump wants to output sometime to put into the newly created table. The first statement updates the mytable with data – which is a soap document. There can be more lines of data – both in this insert statement and there can be more insert statements.

If you run it, you get a warning and nothing else. If you change the name of the tables, you get an error, so NO error is good. If you run it twice, you get an error on importing the GUID twice.

So – no news is good news.

Posted in Uncategorized

interesting use of temp table

i am not King of the temp tables, although i seem to use them alot.

Currently, i am extracting data into a series of temp tables to prepare a data-set to save into a standard database table.  The first step is to query data from a core table into a temp table that i mess with, rework, et al.  But i found that the underlying core table, for a certain market, did not total up to the extracted data – for that same market.  The totals should be the same.  I DID find core data that was not in my temp table, but didn’t understand why.

Turns out that i was making a join to another table, based on a market value and a pseudo-code (pcode) value.  If there was, and there were, entities that didn’t have a pcode, i could not get them into the temporary table.  I was prepared to select them with a case statement and give them a name, which would have solved the data total issue, but they were not being selected with the join.

My supervisor suggested a left (outer) join between the core data and the second table.  The left join returns everything selectable from the core table and when possible, adds the data from the second table.  He told me what to do – i set it up, ran it,  and after 4 minutes casually mentioned that when i had tried the left outer join on this table, it had taken this long, and then i cancelled.  Without the left join, the entire process (using a sparse dataset) returned in 10-20 seconds.  We cancelled.

I mentioned that the second dataset did not have a ‘null’ pcode column in it – could we update it?  No, but it was small and we COULD create a temporary table from it, adding the null data to it.  We did so, added the code to the main script, ran it with a join from the temp table, it finished in seconds, and all was good.  Including the data now matched.

The temp table was a select into from the original table, and a union to a ‘copy’ of the table with the new null values in it.  It looked like

select * into #myTemp FROM [Product].[dbo].[out_market_def]
UNION
select distinct '', market, '' pcode, '' porder, 'all other' , mkt_name, mkt_long, 0, 0  
FROM [product].[dbo].[out_market_def]

The join was to the #myTemp.  We did give the blank pcodes a name (“all other”) but i wanted to take these blanks and give them a new pcode (“p99” and a name “All Other”) which i did with a case statement.  this would work now since we were joining and returning rows with a pcode that was blank – the case statement says if the pcode is blank then set it to ‘p99’ else use the value:

case when c.pcode = '' then 'p99'  else c.pcode end as pcode, 

I would have used a similar construction to update the pcode_name as well, but that was taken care of by the ‘all other’ above. 

 

Posted in Uncategorized

MS SQL 2014 cardinality estimator

Apparently, MS SQL 2014 has a new cardinality estimator. Goodie. Ah, what was the old one? And why is it a cardinal and not a blackbird or robin?

The Query Optimizer picks among one or more query plans to pick the ‘cheapest’ plan. Cheapness is based on CPU and I/O costs, and an important part of that is related to the number of rows of data returned at various steps of the query. The cardinality estimator determines, or rather guesses, this count as well as the distribution of values, value counts and duplicate counts. It uses statistics and heuristics to make the estimate.

If the CE overestimates the number of rows, the costs will be wrong and the right query might not be chosen, specifically
• Selection of a parallel plan when a serial plan might be more optimal.
• Inappropriate join strategy selection.
• Inefficient index navigation strategies (scan versus seek).
• Inflated memory grants.
• Wasted memory and unnecessarily throttled concurrency.

if the number of rows is underestimated
• The selection of serial plan when parallelism would have been more optimal.
• Inappropriate join strategies.
• Inefficient index selection and navigation strategies.

Kendra Little at BrentOzar.com has a query that does not optimize well for 2012 and earlier. It is designed to confuse the Optimizer and estimates it will return millions of rows. The 2014 version handles it well. 

Upgraded servers or databases that are migrated or restored from an older version will not use the new CE.

Paul White (http://sqlperformance.com/2014/01/sql-plan/cardinality-estimation-for-multiple-predicates) has several nice articles about this, including getting histogram data directly from the statistics (“When a predicate makes a simple comparison between a column and a scalar value, the chances are good that the cardinality estimator will be able to derive a good quality estimate”). Multiple column predicates (where A and B) can be equally exact except that we can’t tell from the histograms how much overlap there is between a and b, or rather there are rows that satisfy only A, which satisfes only B and where both A and B are true in the row are are true.

In the old CE, we assume that the two attributes are distributed independently of each other. In this case, if selectivity is the fraction of returned values divides by the total values, over all selectivity is s(A)*s(B) for conjustion (AND) or s(A) + s(B) – (s(A)*s(B))

— uses the new CE on MS SQL 2014 – level 120 is the latest version level.
ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL=120;
GO

— uses the old CE
ALTER DATABASE AdventureWorks2012 SET COMPATIBILITY_LEVEL=110;
GO

 

 

Posted in Uncategorized

string and not a string

The ‘preferred’ way to send email to several people from powershell is to use the send-mailmessage cmdlet.  In our environment, the server needs to be identified as an acceptable server (that is done in a group in the BigIP local traffic manager) else the traffic is not replayed to the SMTP server.  But anyway….

-to switch takes an email address.  -cc and -bcc of course allow more folks to get the message, but to send the email to multiple recipients, you can put the email addresses into an array.  The ‘comma’ operator is the array constructor so”

$emailaddresses = “abc@university.edu”,”def@university.edu” ought to work.  Nope.  Wrong format.

Array, huh?

[array]$emailaddresses = “abc@university.edu”,”def@university.edu” ought to work.  Nope, complains about a string

string,huh?

[string]$emailaddresses = “abc@university.edu”,”def@university.edu” ought to work.  Nope, complains about an object

finally, got a google search hit and found that

[string[]]$emailaddresses = “abc@university.edu”,”def@university.edu” ought to work.  It does.

$emailaddresses|gm and $emailaddresses.gettype() both allowed me to see objects and strings, but something about powershell wanted, in this case, an explicit cast to string array.  Problem solved, heading for the next problem.

Posted in Uncategorized

photoroster and attendance

We have a page of students for each class.  It is currently done in asp/vbscript, although that could change.  Boss wants to turn this into an attendance form.  Click on the student image and mark them as present/absent.  I initially proposed a button below each image and a database to  update, but he seems to want a more intuitive graphic interface.

OK – i created a png image.  It is blank.  Another image says “present” on the bottom and a third says “absent” with an ‘X’ on it.  On my test page (all html coded), i wrapped a form around the table of images.  Using css positioning, i overlay each student image with another image – the blank.png.  If you click on the face,  you are actually clicking on the blank.png.  In that case, use javascript to change the source  of the image from ‘nothing.png’ to ‘present.png’.  The image on the page immediately updates.  If  you click again, you toggle the src from ‘present.png’ to ‘absent.png’ and if again back to ‘nothing.png’.  

So you can now look at the images, determine who is present and who is absent, and mark them so on the page by clicking.  How do we encode this information and put it into a form?  The action of the form is to post to a datahandler that will take the form information (the day of the attendance, the name of the class/section and perhaps the name of the instructor?) and write that to the database.  On the form, each student image has an source.  That same source, minus the file type extension (image.jpg), is used as an ID for the blank image and the blank image is given a class of attendance.  

On the page, in addition to input items for the course, date, et al., there is a hidden input item, with the same ID as the blank image (with ID appended to the name), and when you change the src of the ID by clicking on it and changing the source, the value of the hidden ID is changed from ‘nothing’ to ‘present’ and then to ‘absent’.

The values of the hidden items could be submitted directly, but we don’t know how many of them we are (which we COULD get from the request object).  Instead, i decided to put all the ‘attendance’ names into a collection, and write the ‘ID|value’ into an array item: “lkerardiID | present”.  This is attached to yet another hidden input, (i may not need all these inputs) and submitted when the form is submitted.  

The data handler will get the array, separate it, write a set of SQL insert statements and update the database.

The NEXT issue is to move the code into the original form, and then to do something to get useful reports out of the database.  

Posted in Uncategorized